COLOR CELL DEPENDING ON CODE

[dennisdjones]

In a pre-determined column, if I was to enter letters, such as; E, U/C, P, W, O (no commas, just letters) then background color for the entire row would change to a pre-determined color.

Example:
E=Red
U/C=Turquoise
P=Bright Green
W=Yellow
O=Orange

Appreciate your expertise!

[Von Pookie]

Do a search on conditional formatting.

You have more than 3, so you will need to look for VBA code.

[Jack in the UK]

Hi Kristy the code i sent you via email to day will do just this OK the guy will need to edit it a bit, but ive fullt commented it line by line, post it if you like!

[Jack in the UK]

Hear is yesterday code i wrote you can add in as many conditions as you likem i see you need 5 and i have commented all the code so you can easy edit, let me know if you get stuck


Sub Jacks_Rows_Color() ' Written by Jack in the UK 21 May 2002

On Error GoTo JACKintheUK
' Jack set error handle

Sheets("J").Select
' Jack selets the required sheet
' Edit as required
' Jack change as needed

Range("A1:R50000").Select
' Jack select the required range (cange as to suite requirenments)
' Jacks tip only select smallest range as will slow the macro speed
Selection.Interior.ColorIndex = xlNone
' Jack removes any colours in the above range

Range("A1").Select
' Jack hey i choose a cell
Application.ScreenUpdating = False
'Jack removes screen updates FASTER code this way
JACK = 1
' Jack sets Jack = 1
'>>>>>>>>>>>>>>>>>>>>
' Jack says Lime Green you would like for a YES????
With Sheets("J") 'Jack in the UK
For YES = 1 To WorksheetFunction.CountIf(.Columns("B:B"), "YES")
Set RYESFoundCell = .Columns("B:B").Find(What:="YES", _
After:=.Cells(JACK, 2))
RYESFoundCell.EntireRow.Interior.ColorIndex = 4
'1= black 2= white 3= red 4=lime green 5= blue 6= yellow
JACK = RYESFoundCell.Row
Next YES
End With
'>>>>>>>>>>>>>>>>>>>>>
' Jack says Red you would like for a No????
With Sheets("J") 'Jack in the UK
For N = 1 To WorksheetFunction.CountIf(.Columns("B:B"), "N")
Set RNFoundCell = .Columns("B:B").Find(What:="N", _
After:=.Cells(JACK, 2))
RYESFoundCell.EntireRow.Interior.ColorIndex = 3
JACK = RNFoundCell.Row
Next N
End With
'>>>>>>>>>>>>>>>>>>>>>
' Jack says Red you would like for a YES????
With Sheets("B W") 'Jack in the UK
For Maybe = 1 To WorksheetFunction.CountIf(.Columns("B:B"), "Maybe")
Set RMaybeFoundCell = .Columns("B:B").Find(What:="Maybe", _
After:=.Cells(JACK, 2))
RMaybeFoundCell.EntireRow.Interior.ColorIndex = 6
JACK = RMaybeFoundCell.Row
Next Maybe
End With
'>>>>>>>>>>>>>>>>>>>>>

Columns("C:IV").Select
Selection.Interior.ColorIndex = xlNne
' Jack rips the colour from the right (removes it)
' set as required
' (Dont really need (left in case) dirty who cares?

Application.Goto Reference:="R1C1"
'Jack returns to a cell in this case A1
'(can be any i guess)
Application.ScreenUpdating = True
' Jack sets updtes to live
JACKintheUK:
' Jack compleate error handle
End Sub