Changing color of cell by a word or phrase

[KDC]

I need a cell to change color (update 15 colors) by a specific word or
phrase. I also need to understand where I can find the color index numeric
values?

 

[Rick Rothstein]

Can you give more detail about the first part of your question... One word,
15 cells or 15 words for 15 cells?

For your second question, run this macro... the row number will be the
ColorIndex for the colors shown in Column A...

Sub ShowColorIndexValues()
Dim X As Long
For X = 1 To 56
ActiveSheet.Cells(X, "A").Interior.ColorIndex = X
Next
End Sub

 

[Shane Devenshire]

Hi,

One easy way is to record a macro as you change the color of cells. The
point being that the number (in 2003) aren't related to a color they are
related to the position on the color palette, and since you can modify the
palette any number can be used to represent any color.

If this helps, please click the Yes button.

Cheers,
Shane

 

[KC]

Thanks for your response, 15 words or phrases for 15 cells. Also, regarding
the color index, how do I know that 1=white, 2=yellow and so on? Thanks

 

[David McRitchie]

Three things to check out., perhaps with more specific example
of what you want could be a bit more specific in a reply.

Conditional Formatting (for 3 color combinations, plus default)
http://www.mvps.org/dmcritchie/excel/condfmt.htm

a change event macro (installed differently from normal macros)
would allow you to use many different colors
Event Macros, Worksheet Events and Workbook Events
http://www.mvps.org/dmcritchie/excel/event.htm

Since you asked about color index numbers
Color Palette and the 56 Excel ColorIndex Colors
http://www.mvps.org/dmcritchie/excel/colors.htm

 

[KC]

David, thank you regarding the index this answers my question. I believe now
I need to understand how to write in a cell that if the text =Hello then
color the cell pink; if the text =good bye then color the cell red and so on
for 8-10. Can you help me with the if statement for 10 different scenarios?

 

[David McRitchie]

A word of caution, having more than 5 colors would probably be bad design,
but you can extend the example as you see fit.

What you are asking about would be an Event macro, are the cells text
constants, or are they formulas.

Text is a better, since the cell you test is the same cell identified for the
test, and is also the same cell as you change the coloring in.
http://www.mvps.org/dmcritchie/excel/event.htm#case

If you used a formula you would have to idenify the none formula
cell on the same sheet that you actually change and test the value
of the cell that gets changed.

This is an Event macro and will only apply to the one tab.
Right-click on the worksheet tab, choose View code
and paste in the following code., replacing original Option Explicit
with all of the following. Change the range to cells you want
to change.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'David McRitchie, 2000-08-08 rev. 2000-08-14
' http://www.mvps.org/dmcritchie/excel/event.htm
' modified in newsgroup reply 2009-02-25 by D.McR
Dim vText As String
Dim vColor As Long
Dim cRange As Range
Dim cell As Range
'***************** check range ****
Set cRange = Intersect(Range("A:A"), Range(Target(1).Address))
If cRange Is Nothing Then Exit Sub
'**********************************
For Each cell In Target
vText = UCase(Trim(cell.Value))
'see colors.htm for use of ColorIndex
' http://www.mvps.org/dmcritchie/excel/colors.htm
vColor = 0 'default is no color
Select Case vText
Case "HELLO"
vColor = 3 'Red
Case "GOOD BYE"
vColor = 38 'Pink
Case "C"
vColor = 39
Case "D"
vColor = 41
Case "E"
vColor = 34
Case "F"
vColor = 37
Case "G"
vColor = 35
End Select
Application.EnableEvents = False 'should be part of Change macro
cell.Interior.ColorIndex = vColor
Application.EnableEvents = True 'should be part of Change macro
Next cell
'use to color cell to right
' cell.Offset(0, 1).Interior.colorindex = vColor
'Use to color the entire row
' cell.EntireRow.Interior.ColorIndex = vColor
'To color Text use text instead of Interior
End Sub

 

[KDC]

Thank you, I will try this and yes I am using text.