Posts by rajt85

rajt85 · May 22nd 2010

Hi guys, I have a conditional statement over thousands of cells....

This is in a module:

Code

Sub colourcells()
    Dim icolor As Integer, r As Range
    For Each r In Range("L3:AI6722")
        If IsDate(r.Value) Then
            If Date < r.Value Then
                icolor = 43 'green
            ElseIf Date > DateAdd("m", 1, r.Value) Then
                icolor = 3 'red
            Else
                icolor = 45 'orange
            End If
        Else
            icolor = 2 ' white
        End If
         
        If Not IsEmpty(r.Offset(, 1)) Then
            icolor = 2 ' white
        End If
         
        r.Interior.ColorIndex = icolor
    Next
End Sub

Display More

This is in a worksheet:

Code

Private Sub Worksheet_Calculate()
    Call colourcells
End Sub

As you can see, the range is L3:AI6722.... This goes VERY VERY slow, up to 50+ seconds

There are a few problems with my current coding:
1. Very slow
2. It only updates when there is a calculation made (not when data is entered, which is what i want)

Possible solutions for both my problems that i dont know how to do is:
- Only check conditional formatting when data is entered into a cell that is right of a cell.

e.g.
----L------M------N-----O-----P-----Q-----.........
1 2/2/06 ------- 2/2/06 ------ 2/2/06
2 2/2/06 ------- 2/2/06 ------ 2/2/06
3 2/2/06 ------- 2/2/06 ------ 2/2/06
...

So all i really need to do is make the conditional formatting apply to columns L,N,P etc.... and for example only check the formatting for L1 when L1 and/or M1 is changed. (The data, not actually when calculations are made like my above solution)

PLEASE PLEASE PLEASE help me, this is the last thing for my final solution. This has taken me a while and i just wanna get it over and done with

Cheers,
:music:
Raj

rajt85 · Mar 1st 2007

Very Slow Conditional Formatting In VBA

Hi guys, I have a conditional statement over thousands of cells....

This is in a module:

Code

Sub colourcells()
    Dim icolor As Integer, r As Range
    For Each r In Range("L3:AI6722")
        If IsDate(r.Value) Then
            If Date < r.Value Then
                icolor = 43 'green
            ElseIf Date > DateAdd("m", 1, r.Value) Then
                icolor = 3 'red
            Else
                icolor = 45 'orange
            End If
        Else
            icolor = 2 ' white
        End If
         
        If Not IsEmpty(r.Offset(, 1)) Then
            icolor = 2 ' white
        End If
         
        r.Interior.ColorIndex = icolor
    Next
End Sub

Display More

This is in a worksheet:

Code

Private Sub Worksheet_Calculate()
    Call colourcells
End Sub

As you can see, the range is L3:AI6722.... This goes VERY VERY slow, up to 50+ seconds

There are a few problems with my current coding:
1. Very slow
2. It only updates when there is a calculation made (not when data is entered, which is what i want)

Possible solutions for both my problems that i dont know how to do is:
- Only check conditional formatting when data is entered into a cell that is right of a cell.

e.g.
----L------M------N-----O-----P-----Q-----.........
1 2/2/06 ------- 2/2/06 ------ 2/2/06
2 2/2/06 ------- 2/2/06 ------ 2/2/06
3 2/2/06 ------- 2/2/06 ------ 2/2/06
...

So all i really need to do is make the conditional formatting apply to columns L,N,P etc.... and for example only check the formatting for L1 when L1 and/or M1 is changed. (The data, not actually when calculations are made like my above solution)

PLEASE PLEASE PLEASE help me, this is the last thing for my final solution. This has taken me a while and i just wanna get it over and done with

Cheers,
:music:
Raj

rajt85 · Mar 1st 2007

Re: Conditional Formatting In Vba

that table didnt come out as i wanted...

G,I,K,M are spose to have dates under them

H,J,L,N are spose to be empty...

rajt85 · Mar 1st 2007

Re: Conditional Formatting In Vba

yeh sorry jindon, it was the end of a LONG day so i wasn't thinking right. But your solution works well. Thanks for that.

My spread sheet is 24 colums wide and 6000 rows long.... it takes 50sec+ everytime i re-calculate something coz of the conditional formatting... If i dont have conditional formatting on it only takes a few seconds.... Is there a way to make the conditional formatting only re-calculate for a specific cell rather than every cell?

e.g

.... G | H | I | J | K | L | M | N | ....
1 2/2/07 2/2/07 2/2/07 2/2/07
2 2/2/07 2/2/07 2/2/07 2/2/07
3 2/2/07 2/2/07 2/2/07 2/2/07
4 2/2/07 2/2/07 2/2/07 2/2/07
5 2/2/07 2/2/07 2/2/07 2/2/07
6 2/2/07 2/2/07 2/2/07 2/2/07
....

so when i put in a value in H, J, L, N etc i only want it to conditionally format the cell left of it... i.e if i modify H1, i only want it to conditionally format G1. This will hopefully cut calculation time from 50+sec to a few seconds.

Also, if i put data in a non date cell ( H, J, L etc) the cell left of that should be white (coloured if there is data in it). It only changes if a calculation is made, not when i type the data in (which is what i want). I understand this is happening becasue it is the way i coded it (putting the conditional formatting under 'Private Sub Worksheet_Calculate()'). Is there a way to make the formatting apply only when there are values change in the cells G,H,I,J,K etc????

Cheers :music:

rajt85 · Feb 28th 2007

Re: Conditional Formatting In Vba

Yeh it is kinda what i wanted, but i needed it to be every 2nd row from the starting one.... can i do that?

rajt85 · Feb 28th 2007

Re: Conditional Formatting In Vba

A bit more info that might help.....

The sheet is 5000+ rows so its not running too flash.... ive only generated data for 100 rows and ive got a lil wait...

Rows L, N, P, R... etc are my data rows.... I really only want the colours to change if those rows or the rows next to them change. Is it possible to restrict it to that to make it more efficient?

Also, the colours dont change on a reopen, or when i put something in the adjacent cell unless i rerun the formula in the cells. Anyway around that?

rajt85 · Feb 28th 2007

Re: Conditional Formatting In Vba

You are a mad man!!! that worked a charm. Thank you very much..... 1 last request if you dont mind...... I want that to work with multiple colums, I can add one more range in the code but thats all....

my question to you is. Do I need another set of code for every 2 columns i want this code to affect??.... There is 12+ i want to apply this too and i dont really want 6+ functions for it. For sake of efficiency is there a better way?

once again thank you very much.

rajt85 · Feb 28th 2007

Re: Conditional Formatting In Vba

--------------------------------------------------------------------------------

1) write a code to loop through the range to change the colour of the cells.

2) call 1) from Worksheet_Calculate event

Im thinking option 2 will be better, but i honestly have no idea to do either :confused:

im new at VBA so if you could tell me how to do either that would be really really good. THANKS

rajt85 · Feb 28th 2007

Re: Conditional Formatting In Vba

somebody PLEASE PLEASE PLEASE PLEASE help me
:music:

rajt85 · Feb 28th 2007

Re: Changing Cell Parameters? Vba

hey everyone, i decided to make 4 seperate UDF's and that seems to work fine. Thanks for youe help.

rajt85 · Feb 27th 2007

Hello, I need to use conditional formatting in VBA becasue the conditional formatting option in excel is restricted to only 3 seperate conditions...

I looked up a website that told me to do this but it is not very successful. The code functions the way its supposed to in terms of colours changing to the value of the cell BUT only when i modify each cell sepertly... What i mean is that if i put a formula in one of the cells and drag it down, it does nothing. (colours dont change). Only when i enter the formula seperately for each cell does it work :confused:

Heres what i got...

Code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
    If Not Intersect(Target, Range("L3:L6722")) Is Nothing Then
        If IsDate(Target) Then
            If Date < Target Then
                icolor = 43 'green
            ElseIf Date > DateAdd("m", 1, Target) Then
                icolor = 3 'red
            Else
                icolor = 45 'orange
            End If
        Else
            icolor = 2 ' white
        End If
        
        If Not IsEmpty(Target.Offset(0, 1)) Then
            icolor = 2 ' white
        End If
        
        Target.Interior.ColorIndex = icolor
    End If
End Sub

Display More

First time user of VBA here so any help would be great. Thanks

rajt85 · Feb 27th 2007

Re: Changing Cell Parameters? Vba

I havent actaully started yet so i cant post samples ... im just trying to figure out the best way to do it before i tackle the problem...

My columns are as follows: (with irrelevant stuff not included)

So 'FREQ' is just the time in months till next date..... 'CONTRACT ACTIVATION DTE' is there jsut for the purpose to determine which month to work from and 'Pred#n' represents the predicted month divided into quarters coz the frequency will never be less than every 3 months.....

eg. if 'CONTRACT ACTIVATION DTE' month is feb, and FREQ is 6.... then 'Pred#1' would be FEB, 'Pred#2' would be nothing, 'Pred#3' would be JULY, 'Pred #4' would be nothing.

I hope this helps you help me : D

Thankyou once again.

p.s I was thinking of making 4 seperate user defined functions (PredictQ1, PredictQ2, PredictQ3, PredictQ4) for predicting each quarter seperately.... with paramters freq, activation date and the other 3 quarters... This way each function will know about the other quarters and konw if its empty, got a month etc

What you think?

rajt85 · Feb 27th 2007

Re: Changing Cell Parameters? Vba

So whats the best way of acheiving what i want to do? can i make a button in excel and then make that button calculate dates for the fields i want to calculate it in?
Thanks

rajt85 · Feb 27th 2007

Re: Changing Cell Parameters? Vba

Another method i have come across is to not pass the cells i want to change as parameters, but change them directly using the User Defined Function. I found the following code:

Code

Range("N8").Value = 44

but it doesnt seem to work... i just get the error "#Value"

:crying:

rajt85 · Feb 27th 2007

Hi, I am creating a user defined function in excel VBA. What it is supposed to do is predict dates. The sheet has frequency of events per year (max 4) and last event date. I am thinking to have parameters: frequency, last date, q2date, q3date, q4date...

so basically i want to type the function name (PREDICT) in the q1date column and have the function generate data in the q2date, q3date and q4date columns by using the cell reference in the parameters.... is that possible?

I searched the net for solutions, i came across a 'ByRef' command but with no success to my function

Any help would be greatly appreciated... this is my first time using VBA. THANKS