Conditional Formats >3 over a range

[ZSandman]

Greetings: I've been searching thru the Message Board CD (which is great by the way) for some help with Conditional Formatting. I am trying to apply conditional formatting with 5 (or more) rules, but covering various ranges in the same worksheet. I found the following VBA which does the trick, but it's limited to a single cell. I've tried to expand it to cover a range, but no luck and many colorful errors. The VBA would limit the formatting to the desired ranges (like the second row of the code limits it to cell A1). Any suggestions greatly appreciated. Thanks.
Sandman

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Or Target.Cells.Count > 1 Then Exit Sub
Select Case [A1].Value
Case "A"
Target.Interior.ColorIndex = 3
Case "P"
Target.Interior.ColorIndex = 6
Case "B"
Target.Interior.ColorIndex = 8
Case "T"
Target.Interior.ColorIndex = 22
Case "R"
Target.Interior.ColorIndex = 25
Case Else
Target.Interior.ColorIndex = 15
End Select
End Sub

 

[Joe4]

Can you list the conditions & rules you are trying to program?

 

[pgc01]

Hi ZSandman

To use it on a cell in a range, try replacing the beginning of the Event sub by:

Private Sub Worksheet_Change(ByVal Target As Range) 
If Not Intersect (Target, Range("A1:D15")) Is Nothing then
Select Case Target.Value
....

Then try changing any cell in the range "A1:D15" to test the conditional formatting.

Hope this helps
PGC

 

[ZSandman]

Thanks for the quick replies.

JM14: The conditions are simply if I enter an "A" in a cell, the cell color changes to red, entering "B" changes the cell color to blue, "C" changes the cell color to green, etc.

ZSandman

 

[Joe4]

But you talked about changing a whole range of cells.

I assume you mean something like, if I enter "A" in a cell, change the whole row (or some other range) to red, etc.

So what is the correlation between the cell you are entering the value in, and the range you want to change the color of?

 

[ZSandman]

PGC01:
Sorry, but getting error message "Compile Error - Block If without End If" with the End Sub highlighted. To be sure it's not user error on my part, I deleted the first 3 lines of the original VBA and replaced it with the 3 lines you kindly provided.

 

[ZSandman]

JM14: Sorry for confusion. What I'm trying to do is apply more than 3 conditional formats to cells in more than one range. Say the ranges are A1:M1 and A3:M3. I want each of the cells in those ranges to have conditional formatting such that if I enter "A" into cells A1 and M3, only those two cells are shaded red. If I then enter "B" in cell B1, that cell turns blue, cells A1 and M3 stay red, and no other cells are colored.

 

[Joe4]

Then pgc01's method should do exactly that. Based on the error message you said you were getting, it sounds like you may have left off the "End If" or "End Select" statement.

 

[pgc01]

Hi again

getting error message "Compile Error - Block If without End If"

The code I provided starts an If block, as jm14 already said you have to close it.
Insert "End If" between your 2 last statements

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:D15")) Is Nothing Then
        Select Case Target.Value
            Case "A"
                Target.Interior.ColorIndex = 3
            Case "P"
                Target.Interior.ColorIndex = 6
            Case "B"
                Target.Interior.ColorIndex = 8
            Case "T"
                Target.Interior.ColorIndex = 22
            Case "R"
                Target.Interior.ColorIndex = 25
            Case Else
                Target.Interior.ColorIndex = 15
        End Select
    End If
End Sub

HTH
PGC

 

[ZSandman]

Hi pgc01: Many thanks. Works like a charm.
ZSandman