VBA conditional formatting (Excel 2003)

[PTD]

I've searched and seached, and haven't found the solution I'm looking for. I have 5 conditions, and as you know Excel only allows 3 conditional formats. I have little VBA experience, so I can't seem to create this on my own.

I'm trying to fill the color of a cell based on the value in different cells. Said differently, if the value in cell A1 = cell A42 then fill color green, if A1 = cell B42 then fill color red, and so on for 5 different reference points. The default would be to fill color black. Does anyone have some sample VBA code that will do something like this?

 

[bertie]

You could do this using a worksheet_Change event.

To use the code, in Excel
press Alt + F11
in the Project window on the left double click the worksheet in question
copy and paste the code

Here is a colour index link:
http://www.mvps.org/dmcritchie/excel/colors.htm

[COLOR=#000000][COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
 
   [COLOR=green]'set the range to trigger the change event[/COLOR]
   [COLOR=darkblue]If[/COLOR] Intersect(Target, Range("A1")) [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
   [COLOR=green]'default colour[/COLOR]
   Target.Interior.ColorIndex = 1 [COLOR=green]'black[/COLOR]
 
   [COLOR=darkblue]If[/COLOR] Target.Value = Range("A42").Value [COLOR=darkblue]Then[/COLOR]
      Target.Interior.ColorIndex = 4 [COLOR=green]'green[/COLOR]
      [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
   [COLOR=darkblue]If[/COLOR] Target.Value = Range("B42").Value [COLOR=darkblue]Then[/COLOR]
      Target.Interior.ColorIndex = 3 [COLOR=green]'red[/COLOR]
      [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
   [COLOR=green]'etc[/COLOR][/COLOR]
[COLOR=#000000][COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/COLOR]

 

[PTD]

That works! Thank You!

 

[PTD]

So it worked for the first cell reference, however when I added in the next section of code nothing happens (the first reference still works). I'm sure I'm doing something wrong, but I can't figure it out. Here's the code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("N64")) Is Nothing Then Exit Sub

Target.Interior.ColorIndex = 1

If Target.Value = Range("N80").Value Then
Target.Interior.ColorIndex = 4
Exit Sub
End If

If Target.Value = Range("N94").Value Then
Target.Interior.ColorIndex = 35
Exit Sub
End If

If Target.Value = Range("N108").Value Then
Target.Interior.ColorIndex = 36
Exit Sub
End If

If Target.Value = Range("N122").Value Then
Target.Interior.ColorIndex = 44
Exit Sub
End If

If Target.Value = Range("N136").Value Then
Target.Interior.ColorIndex = 7
Exit Sub
End If

If Intersect(Target, Range("M64")) Is Nothing Then Exit Sub

Target.Interior.ColorIndex = 1

If Target.Value = Range("M80").Value Then
Target.Interior.ColorIndex = 4
Exit Sub
End If

If Target.Value = Range("M94").Value Then
Target.Interior.ColorIndex = 35
Exit Sub
End If

If Target.Value = Range("M108").Value Then
Target.Interior.ColorIndex = 36
Exit Sub
End If

If Target.Value = Range("M122").Value Then
Target.Interior.ColorIndex = 44
Exit Sub
End If

If Target.Value = Range("M136").Value Then
Target.Interior.ColorIndex = 7
Exit Sub
End If
End Sub

 

[Andrew Poulsom]

That's because of:

If Intersect(Target, Range("N64")) Is Nothing Then Exit Sub

The procedure ends if Target isn't N64. You can expand the range:

If Intersect(Target, Range("M64:N64")) Is Nothing Then Exit Sub

 

[PTD]

Perfect! Thanks!

 

[PTD]

Now I have a different problem. I'm trying to add a new target range with different criteria. This code below doesn't seem to work. Do I need to do something different with the initial If statement?

If Intersect(Target, Range("N64:D64")) Is Nothing Then Exit Sub
If Target.Value = Range("N80").Value Then
Target.Interior.ColorIndex = 4
Exit Sub
End If
If Target.Value = Range("N94").Value Then
Target.Interior.ColorIndex = 35
Exit Sub
End If
If Target.Value = Range("N108").Value Then
Target.Interior.ColorIndex = 36
Exit Sub
End If
If Target.Value = Range("N122").Value Then
Target.Interior.ColorIndex = 44
Exit Sub
End If
If Target.Value = Range("N136").Value Then
Target.Interior.ColorIndex = 7
Exit Sub
End If

If Intersect(Target, Range("N65:D65")) Is Nothing Then Exit Sub
If Target.Value = Range("M80").Value Then
Target.Interior.ColorIndex = 4
Exit Sub
End If
If Target.Value = Range("M94").Value Then
Target.Interior.ColorIndex = 35
Exit Sub
End If
If Target.Value = Range("M108").Value Then
Target.Interior.ColorIndex = 36
Exit Sub
End If
If Target.Value = Range("M122").Value Then
Target.Interior.ColorIndex = 44
Exit Sub
End If
If Target.Value = Range("M136").Value Then
Target.Interior.ColorIndex = 7
Exit Sub
End If
End Sub

 

[Andrew Poulsom]

Do it this way:

If Not Intersect(Target, Range("N64:D64")) Is Nothing Then
'    Your code if target is in range
ElseIf Not Intersect(Target, Range("N65:D65")) Is Nothing Then
'    Your code if target is in range
End If

 

[PTD]

I get "Compile Error: Block If without End If" using the code below:

If Not Intersect(Target, Range("N64:D64")) Is Nothing Then
If Target.Value = Range("N80").Value Then
Target.Interior.ColorIndex = 4
Exit Sub

If Target.Value = Range("N94").Value Then
Target.Interior.ColorIndex = 35
Exit Sub

If Target.Value = Range("N108").Value Then
Target.Interior.ColorIndex = 36
Exit Sub

If Target.Value = Range("N122").Value Then
Target.Interior.ColorIndex = 44
Exit Sub

If Target.Value = Range("N136").Value Then
Target.Interior.ColorIndex = 7
Exit Sub

ElseIf Not Intersect(Target, Range("N64:D64")) Is Nothing Then
If Target.Value = Range("N81").Value Then
Target.Interior.ColorIndex = 4
Exit Sub

If Target.Value = Range("N95").Value Then
Target.Interior.ColorIndex = 35
Exit Sub

If Target.Value = Range("N109").Value Then
Target.Interior.ColorIndex = 36
Exit Sub

If Target.Value = Range("N123").Value Then
Target.Interior.ColorIndex = 44
Exit Sub

If Target.Value = Range("N137").Value Then
Target.Interior.ColorIndex = 7
Exit Sub
End If
End Sub

 

[Andrew Poulsom]

Put:

End If

immediately before End Sub.