Red Amber Green

[ch1]

Any ideas ?
I need to change the fill colour or the font colour when a word is entered into a cell.

eg
If YES is typed I want the colour to go GREEN
If No is typed in I want it to change to RED
If Maybe is typed in I want it to change to AMBER.

The reason for this is that in the spreadsheet it is visible quickly, but I will also need to transfer the data into a mainframe system so only the text will transfer.

I'm sure I have seen this done years ago, maybe not in Excel though.

Many thanks

[Von Pookie]

Go to FormatConditional Formatting.

You can set up to 3 conditions.

Hope this helps!

[Jack in the UK]

Do you need VBA?

[ch1]

Seen the answer below, format condition.

Strange same problem so close together.

Thanks

[Jack in the UK]

Sorry i hat to write this as it were, NO i do not suggest, and N is better:

This idea of the code comes from my great freind Dave hawley, who taucht me the only way to VBA conditional format, these no limit to how many as is so fast

Just add in a new section as required



Sub Rows_Colour() ' Written by Jack in the UK 21 May 2002
On Error GoTo JACKintheUK
' Jack set error handle
Sheets("B W").Select
' Jack selets the required sheet
' Jack change as needed
Range("A1:R500").Select
'Jack select the required range
Selection.Interior.ColorIndex = xlNne
' 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 Green you would like for a YES????
With Sheets("B W") '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 N????
With Sheets("B W") '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 Yellow you would like for a Maybe????
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


marker condition format