Adding More Conditional Formating

[november678x]

Hello to all,

I realized that Excel has a limit of 3 conditional formats. I read some post on this can be coded into the sheet but they only refered to numbers and not text. Does any know how I can code this into the my sheet? Any help would be appreciated, Thank you


For cell range B7:B26

If text contains "POI" Color background to "Rose"

If text contains "KFI" Color background to "Tan"

If text contains "EPT" Color background to 'Light yellow"

If text contains "POC" Color background to 'Light green"

If text contains "TPN" Color background to "Pale blue"

If text contains "CII" Color background to 'Lavender"

If text contains "OS" Colot background to "Gray-25%"

If text contains "SP" Color background to 'Light orange"

[pm]

november678x wrote:

> Hello to all,
>
> I realized that Excel has a limit of 3 conditional formats. I read some
> post on this can be coded into the sheet but they only refered to
> numbers and not text. Does any know how I can code this into the my
> sheet? Any help would be appreciated, Thank you
>
> For cell range B7:B26
>
> If text contains "POI" Color background to "Rose"
>
> If text contains "KFI" Color background to "Tan"
>
> If text contains "EPT" Color background to 'Light yellow"

Paste this code to sheets code

Private Sub Worksheet_Calculate()
Dim rng As Range
Application.ScreenUpdating = False

For Each rng In Range("B7:B26")

If rng.Value = "POI" Then
rng.Interior.ColorIndex = 2
ElseIf rng.Value = "KFI" Then
rng.Font.ColorIndex = 3
ElseIf rng.Value = "EPT" Then
rng.Font.ColorIndex = 1
'and so on...
End If

Next rng

Application.ScreenUpdating = True
End Sub

rgs

[november678x]

Thanks for the reply. I pasted this into the sheet code but the background color wont change when I enter the codes. Does the version of excel matter? Or is there another variation of this I can try?

Thank you

[pm]

november678x wrote:

> Thanks for the reply. I pasted this into the sheet code but the
> background color wont change when I enter the codes. Does the version
> of excel matter? Or is there another variation of this I can try?

i wrote:

If rng.Value = "POI" Then
rng.Interior.ColorIndex = 2
ElseIf rng.Value = "KFI" Then
rng.Font.ColorIndex = 3

but of course shoul be Interior.ColorIndex (not Font)

[november678x]

It still does not change color when I input the codes. Here is what I have in my sheet code, did I miss something?

Thank you



Private Sub Worksheet_Calculate()
Dim rng As Range
Application.ScreenUpdating = False

For Each rng In Range("B7:B26")

If rng.Value = "POI" Then
rng.Interior.ColorIndex = 2

ElseIf rng.Value = "KFI" Then
rng.Interior.ColorIndex = 3

ElseIf rng.Value = "EPT" Then
rng.Interior.ColorIndex = 1

End If

Next rng

Application.ScreenUpdating = True
End Sub

[november678x]

It still does not change color when I input the codes. Here is what I have in my sheet code, did I miss something?

Thank you



Private Sub Worksheet_Calculate()
Dim rng As Range
Application.ScreenUpdating = False

For Each rng In Range("B7:B26")

If rng.Value = "POI" Then
rng.Interior.ColorIndex = 2

ElseIf rng.Value = "KFI" Then
rng.Interior.ColorIndex = 3

ElseIf rng.Value = "EPT" Then
rng.Interior.ColorIndex = 1

End If

Next rng

Application.ScreenUpdating = True
End Sub

[november678x]

It still does not change color when I input the codes. Here is what I have in my sheet code, did I miss a step?

Thank you



Private Sub Worksheet_Calculate()
Dim rng As Range
Application.ScreenUpdating = False

For Each rng In Range("B7:B26")

If rng.Value = "POI" Then
rng.Interior.ColorIndex = 2

ElseIf rng.Value = "KFI" Then
rng.Interior.ColorIndex = 3

ElseIf rng.Value = "EPT" Then
rng.Interior.ColorIndex = 1

End If

Next rng

Application.ScreenUpdating = True
End Sub