Program countif more than 3

J

Joel

Hi I have three countif statements in my Excel spreadsheet that use
conditional formatting but I need to add a fourth but of couse Excel only
allows three. How can I get around this.

Thank you in advance
 
B

Bob Phillips

Use a default colour for all the cells, then just add 3 condition formats.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
J

Joel

DEar Bob

Thank you what I am after is some code for VBA to turn a cell a certain
colour if a certain letter is typed. Conditional formatting is great but does
not help if you have more than thfree conditions.

For Example ig I type a H I want it to turn the cell green, Bh turn the cell
pink S turn yellow md L turn the cell Red

Thanks Joel
 
B

Bob Phillips

I was suggesting that if you colour those cells all as yellow say, you only
need to test for the other three conditions, so CF works.

But if you must VBA


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "L": .Interior.ColorIndex = 3 'red
Case "S": .Interior.ColorIndex = 6 'yellow
Case "Bh": .Interior.ColorIndex = 7 'pink
Case "H": .Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
J

Joel

Thank you very much
--
N/A


Bob Phillips said:
I was suggesting that if you colour those cells all as yellow say, you only
need to test for the other three conditions, so CF works.

But if you must VBA


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "L": .Interior.ColorIndex = 3 'red
Case "S": .Interior.ColorIndex = 6 'yellow
Case "Bh": .Interior.ColorIndex = 7 'pink
Case "H": .Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
Click to expand...
 
Top