More than three conditional formatting

Topic

New Reply

Hi,

I have more than three conditional formatting need to setup, but I couldn’t do it in excel 2003 because of the limitation. What codes can I use to put under Worksheet_Change event procedure, and it will do the following conditional formatting:

1) cell volume is equal to 100%, the fill color of cell is green
2) cell volume is equal to 95% – 99%, the fill color of cell is light blue
3) cell volume is equal to 90% – 94%, the fill color of cell is yellow
4) cell volume is below 94%, the fill color of cell is red

Thanks in advance.

Regards,

Reply | Quote

Replies

format all cells red, then use your first three items as Conditional Formats.

Reply | Quote

Well, I can’t format all cells to red since some cells have volume for 0%. I will need it with no color.

Reply | Quote

Hello –
If cell volume is 90%, 91%, 92%, 93% it would pass both conditions 3) yellow and condition 4) red. What color should it be?

Is cell volume ever over 100%?

Are cell volumes always rounded to a 2 digit percentage? For example, cell volume would never be 94.5% or 99.3%, etc.

Reply | Quote

Hi Tim,

If the cell volume between 90% to 94%, the color is yellow.
If the cell volume below 94%, the color is red.

No, the cell volume won’t ever over 100%.

Yes, all cell volumes always rounded to a 2 digit percentage.

Thanks,

Regards,

Reply | Quote

Hello – Am I missing something? Lets say cell volume is 92%.

92% is between 90% and 94%, therefore color is yellow.

And

92% is below 94%, therefore color is red.

What color should 92% be?

Thanks,
Tim

Reply | Quote

I am sorry, Tim. You are right.

between 90% and 94%, color is yellow.
below 90%, color is red.

Thanks,

Reply | Quote

Have you tried something like this? Would this work?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer

If Not Intersect(Target, Range(“A1:A50”)) Is Nothing Then

Select Case Target
Case 100
icolor = 4
Case 95 To 99
icolor = 28
Case 90 To 94
icolor = 6
Case 1 To 89
icolor = 3
Case Else
‘Whatever
End Select

Target.Interior.ColorIndex = icolor

End If

End Sub

Reply | Quote

Thanks, Tim.

Reply | Quote