Conditional Formatting more than 3 using reference cells

[Neanie]

I have found a lot of VBA on how to code conditional formatting for more than 3 conditions, all of which are conditioned on the information within the cell.

I have fours cells in a worksheet, each referencing a different cell within the same worksheet. I need to have four conditions to change the color of each. When I use the traditional code, the color will not change with the information in the referenced cell.

This is what I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then
Select Case Target
Case ""
icolor = 16
Case Is < 0.505
icolor = 3
Case 0.5 To 0.704
icolor = 6
Case Is > 0.904
icolor = 4
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub


Also, is there there a way to target the four individual cells rather than having a range.

Thanks in advance for any help!

Jeannine

 

[Gruuuu]

Are you saying that you have 4 cells that contain the thresholds for the conditional formatting?
i.e.:

     A    B    C    D   ...
1    0  .505 .704 .904
2
...

 

[SkipVought]

Jeannine,

Best to post VBA questions in forum707 for maximum results.

HOWEVER, you don't need code to format FOUR conditions!!! Can be accomplished with native Excel features.

Also, is there there a way to target the four individual cells rather than having a range.

Please explain.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Neanie]

The four cells reference four seperate cells:

A1 references H15
A2 references H16
B1 References I15
B2 references I16

As the values change is H15:I16, the colors do not change in A1:B2. The code above only works if the values are changed directly in the targeted cells A1:B2.

I hope this helps clarify.

Thanks for such a quick response!

 

[SkipVought]

Select A1:B2

Format the interior for the default format.

Format CF
[tt]
=H15=""
[/tt]
for your first criteria
and continue thru your remaing three.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}