Conditional Formatting-EXCEL

[khan5118]

You can only have three conditional formatting in excel, does anyone know how to add a forth. I have:
1-if less then make cell yellow
2- if greater then make cell red
3- if between make cell green
I have some empty cells I don't want colored. But I need the conditional formatting there for the next time data is pasted into those cells. (next time there could be values there).
Any help is better then no help at all.

Thank

 

[KathyD]

I don't know if this is much help but could I suggest that you change your greater than and less than conditions to be 'betweens'?

For instance:

condition 1) if cell < 25 then yellow
condition 2) if cell > 700 then red
condition 3) if cell between 25 and 700 then blue

could be changed to:
condition 1) if cell between 0 and 25 then yellow
condition 2) if cell > 700 then red
condition 3) if cell between 25 and 700 then blue

hth,

KathyD

 

[robcarr]

you could do this as a case select in VBA, you can have plenty of options, below changes the colour of the row depending on what is entered into column 5, should get you started,hope it helps. needs to be entered into the worksheet module for which you wish it work on.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Column <> 5 Then
Exit Sub
Else
Select Case UCase(Target.Text)
Case &quot;RICH&quot;
Rows(Target.Row).Interior.ColorIndex = 37
Case &quot;SCOTT&quot;
Rows(Target.Row).Interior.ColorIndex = 6
Case Else
End Select
End If
End Sub

Thanks

Thanks Rob.