Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding More Conditional Formating
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" -- november678x ------------------------------------------------------------------------ november678x's Profile: http://www.excelforum.com/member.php...o&userid=23641 View this thread: http://www.excelforum.com/showthread...hreadid=519433 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding More Conditional Formating
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding More Conditional Formating
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 -- november678x ------------------------------------------------------------------------ november678x's Profile: http://www.excelforum.com/member.php...o&userid=23641 View this thread: http://www.excelforum.com/showthread...hreadid=519433 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding More Conditional Formating
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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding More Conditional Formating
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 ------------------------------------------------------------------------ november678x's Profile: http://www.excelforum.com/member.php...o&userid=23641 View this thread: http://www.excelforum.com/showthread...hreadid=519433 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding More Conditional Formating
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 ------------------------------------------------------------------------ november678x's Profile: http://www.excelforum.com/member.php...o&userid=23641 View this thread: http://www.excelforum.com/showthread...hreadid=519433 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding More Conditional Formating
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 -- november678x ------------------------------------------------------------------------ november678x's Profile: http://www.excelforum.com/member.php...o&userid=23641 View this thread: http://www.excelforum.com/showthread...hreadid=519433 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Conditional Formating / | Excel Worksheet Functions | |||
Word Formating & Words Adding | Excel Discussion (Misc queries) | |||
Install dates formating using conditional formating? | Excel Discussion (Misc queries) | |||
Conditional formating | Excel Programming | |||
Adding percentages in a column and automatic formating of contents | Excel Worksheet Functions |