Help needed setting up 4 conditions

  • Thread starter Thread starter Blue
  • Start date Start date
B

Blue

I want to set up 4 conditions
=B5 +2% format cell GREEN
B5+2% format cell BLUE
Click to expand...
<=B5-2% format cell YELLOW
<B5-2% format cell RED

B5 = 100
100 to 102 Green
102 to higher number Blue
98 to 100 Yellow
98 to lower number Red

Cell number and percentage change from sheet to sheet

Confused and need help

Blue
 
Hi Rick
conditional format only accepts 3 conditions though you have a fourth
if you include the default format.

Hi
conditional format only accepts 3 conditions though you have a fourth
if you include the default format.

If you only want to apply different FONT colors based on NUMBERS, you
can define up to 6 different styles. See:
http://www.mcgimpsey.com/excel/conditional6.html
for instructions how to do it

For everything else you'll need VBA code (e.g. process the
worksheet_change event and apply your format based on the cell values).
The following will color the entry in cell A1:A100 based on its value:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Red": .Interior.ColorIndex = 3
Case "Blue": .Interior.ColorIndex = 10
'etc.
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub
 
I want to set up 4 conditions

<=B5-2% format cell YELLOW
<B5-2% format cell RED

B5 = 100
100 to 102 Green
102 to higher number Blue
98 to 100 Yellow
98 to lower number Red

Cell number and percentage change from sheet to sheet

Confused and need help

Blue
Click to expand...

Format the cell as RED.

Then use Conditional Formatting to set up your other conditions.

For the example you give, if the cell to be conditionally formatted is also B5,
then set up your conditions as:

Value Is: between 98 to 100 --> Yellow
Value Is: between 100 to 102 --> Green
Value Is: greater than 102 --> Blue

If no condition is met, the cell will format as Red.

Be sure to check the boundaries (98,100,102) so the formatting is how you want
and not on the previous or next condition.

If you need more than four conditions, you will need to use an event driven
macro.


--ron
 
Back
Top