VBA for Conditional Formatting

[alza3eem999]

Hello
1st time using forum so sorry in advance for any mistakes!
i tried to create small code in VBA to bypass the 3-max limitations of conditional formatting in excel (2003)
my requirements are as follows:
turn cell RED if value>=50
turn cell YELLOW if value<50 and >20
turn cell PURPLE if value <-10
keep cell neutral in all other cases

cell range is say A:1 to A:20
the code worked fine if i typed the values above as numbers in the code. however, if i tried to use a cell-location reference (say cell type 50 in cell C1, 20 in cell C2, and -10 in cell C3), then all the cells turn red by default. they only turn purple when i enter any -ve #, and go neutral when i type in text. i have put the code below

CODE HERE:

Private Sub Worksheet_Change(ByVal Target As Range)
Set test = Range("E1:E20")
For Each Cell In test

If IsNumeric(Cell.Value) = False Then
Cell.Interior.ColorIndex = x1None 'no color

ElseIf Cell.Value >= C1 Then
Cell.Interior.ColorIndex = 3 'red

ElseIf Cell.Value < C1 And Cell.Value > C2 Then
Cell.Interior.ColorIndex = 6 'yellow

ElseIf Cell.Value < C3 Then
Cell.Interior.ColorIndex = 39 'purple


Else
Cell.Interior.ColorIndex = xlNone

End If

Next

End Sub

CODE END

not sure where it's going wrong. again, i am no expert, so sorry if this seems minute! thanks alot

 

[Gary McMaster]

C1, C2 & C3 are being intepreted as variables not cell references. They are probably all zero.

You need something like:

ElseIf Cell.Value >= Activesheet.Range("C1").value Then

If you turn on Option Explicit VBA will alert you to some mistakes like this.

Gary

 

[alza3eem999]

thx. that was the problem. glad i mentioned im a beginner!

 

[Gary McMaster]

Glad you got it working.

As I mentioned, Option Explicit will help you.

In the VBA IDE select Tools > Options

On the "Editor" tab check "Require Variable Declaration"

This will automatically place the "Option Explicit" statement in your modules and require you to declare variables. Had this been turned on VBA would have issued a "Variable not defined" error and highlighted C1 in your code. This would have served as clue that C1 was not what you thought it was.

It will also help eliminate some typos. Like the one in this line:

cell.Interior.ColorIndex = x1None 'no color

"x1None" is misspelled and that line will not work as expected

Gary