Conditional Formats >3 over a range

ZSandman

New Member
Joined
Nov 4, 2002
Messages
20
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
Can you list the conditions & rules you are trying to program?
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,837
Hi ZSandman

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

Code:
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

New Member
Joined
Nov 4, 2002
Messages
20
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Nov 4, 2002
Messages
20
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

New Member
Joined
Nov 4, 2002
Messages
20

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,837
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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,956
Messages
5,525,883
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top