Cell Colours

[Eddiec]

I have been trying to find an answer to my question for months, but to no
avail.

I need to change the colours of cells based on different words I type in.
For example "Deployed" = Red Cell, "Awaiting" = Blue Cell etc... Everywhere I
look people say use Conditional Formatting, however, this only allows three
changes and I have a minimum of 10. From what I can gather I need to program
some VB but unfortunately VB may as well be "Klingon".

I guess what I am asking is - could anyone provide a quick VB script that
could allow me to do what I need?

Pleeeeeeeeeeez help

 

[Luke M]

Correct, you'll need to use VB. But we'll try to help you out. ;-)
First, right click on your sheet tab, view code. Paste the following in:

'=======
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'Define the range you want conditioned
For Each cell In Range("B:B")
x = cell.Value
Select Case x

'Copy this style as needed
Case "Bob"
cell.Interior.ColorIndex = 1
Case "Tom"
cell.Interior.ColorIndex = 2
Case "Smith"
cell.Interior.ColorIndex = 3
'If you want to modify font, use following format:
cell.Font.ColorIndex = 1

End Select
Next cell
Application.EnableEvents = True
End Sub
'===========


You'll notice the part at the beginning where you define the range you want
to look at. Set this up to include the cells your want "formatted".

Also, the section with the various cases is where you setup your different
scenarios. The last case has an example of how to define font color. Note
that colors are deifned via numbers, not by words. If you need to create a
"key" to help you know which colors go with which numbers, you can run the
following macro:

'=======
Sub CreateReference()
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = 1
Next
End Sub
'=======

Once you're done editing your macro, close the Visual Basic Editor, and
continue as normal in your workbook.

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A140") 'adjust to suit
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
'define your list of words in vals array
vals = Array("C", "D", "G", "H", "K", "L", "O", "S", "C", "X")
'define the list of colors in nums array
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 23, 15)
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(i) Then
icolor = nums(i)
End If
Next
If icolor > 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
End Sub

This is sheet event code. Right-click on the sheet tab and "View code"

Paste the code into that sheet module.

Edit the range and the vals list and color nums to suit.


Gord Dibben MS Excel MVP

 

[Rick Rothstein]

Give this Change event code a try...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2:K99")) Is Nothing Then
Select Case Target.Value
Case "Deployed"
Target.Interior.ColorIndex = 3
Case "Awaiting"
Target.Interior.ColorIndex = 5
Case "Etc."
Target.Interior.ColorIndex = 10
Case Else
Target.Interior.ColorIndex = xlColorIndexNone
End Select
End If
End Sub

Change my example range of B2:K99 to whatever actual range of cells you want
to have this functionality... use Columns("B") in place of Range("B2:K99")
so cover all of column B (change the B to whatever actual column you want it
to apply to). Replace the "Etc." in my 3rd example Case statement with the
word or phrase you want to look for and add as many other Case
"<<YourWord>>" statements followed by the Target.Interior.ColorIndex
assignments as you need. NOTE: The Case Else sub-block **must** be last in
your list of Case sub-blocks. And, of course, change/use the ColorIndex
values you actually want.

To install this event code, right-click the tab at the bottom of the
worksheet, select "View Code" from the pop up menu that appears and
copy/paste the above code into the code window that appeared. That is it...
go back to the worksheet and enter some of your values into cells within the
range you specified and they should change colors accordingly.

 

[L. Howard Kittle]

Hi Luke,

A typo I think...Cells(i, 1).Interior.ColorIndex = 1
Should be should be...Cells(i, 1).Interior.ColorIndex = i

Regards,
Howard

 

[Rick Rothstein]

Actually, since this is a Worksheet Change event, I don't think the code
should be looping at all; rather, I think the code should be working on the
Target argument the VB automatically provides for this event. See the code I
posted earlier for my take on how this event code should be implemented.

 

[L. Howard Kittle]

I was refering to the little code to make a color index chart 1 through
56...

Sub CreateReference()
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = 1
Next
End Sub

As written, it returns all ColorIndex 1 (Black)

Howard

 

[Rick Rothstein]

Sorry, I missed that... I didn't scroll Luke's message down far enough to
see that routine nor did I try to see what your code was actually referring
to.

 

[eddiec]

Many Thanks for all of your help guys, at long last I have an answer. due to
the fact that I know nothing about VB it was actually Rick who provided the
answer that, with my limited intelligence, I could actually understand and
make sense of. Once again a BIG thanks to all of you

Eddiec