Changing the Cell Background Color when a keyword is entered

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to write a macro to change the cell background color when keywords are entered, something like

IF keyword = "north" THEN cell.Font.Color = Blu
IF keyword = "south" THEN cell.Font.Color = Re
IF keyword = "east" THEN cell.Font.Color = Gree
IF keyword = "west" THEN cell.Font.Color = Orang
IF keyword = "central" THEN cell.Font.Color = Pin

There will definitely be more than 3 keywords so I will not be able to use Conditional Formatting

Any help would be appreciated. Thanks.

Rachael
 
Rachael,

Here is some event code that will handle those values in column B (2).

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 2 Then
Select Case LCase(Target.Value)
Case "north": Target.Font.ColorIndex = 5 'Blue
Case "south": Target.Font.ColorIndex = 3 'Red
Case "east": Target.Font.ColorIndex = 10 'Green
Case "west": Target.Font.ColorIndex = 46 'Orange
Case "central": Target.Font.ColorIndex = 7 'Pink
End Select
End If


ws_exit:
Application.EnableEvents = True

End Sub

Add to the worksheet code module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Rachael said:
I'm trying to write a macro to change the cell background color when
Click to expand...
keywords are entered, something like:
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell as Range
Dim Keyword as String
If Target.count > 1 then exit sub
If target.Column = 3 then
Keyword = lcase(target.value)
Set cell = Target
IF keyword = "north" THEN cell.Font.ColorIndex = 5
IF keyword = "south" THEN cell.Font.ColorIndex = 3
IF keyword = "east" THEN cell.Font.ColorIndex = 4
IF keyword = "west" THEN cell.Font.ColorIndex = 45
IF keyword = "central" THEN cell.Font.ColorIndex = 40
End if
End Sub

Right click on the sheet tab and select view code.

Paste in code like the above. the target.column = 3 limits the action to
changes made in column 3. Change to adjust

--
Regards,
Tom Ogilvy


Rachael said:
I'm trying to write a macro to change the cell background color when
Click to expand...
keywords are entered, something like:
 
Rachael,

Use the Worksheet_Change event procedure. For example, put the
following code in the appropriate worksheet's code module:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
For Each Rng In Target.Cells
Select Case Rng.Text
Case "north"
Rng.Font.ColorIndex = 5
Case "south"
Rng.Font.ColorIndex = 3
' and so on
End Select
Next Rng
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Rachael said:
I'm trying to write a macro to change the cell background color
Click to expand...
when keywords are entered, something like:
IF keyword = "north" THEN cell.Font.Color = Blue
IF keyword = "south" THEN cell.Font.Color = Red
IF keyword = "east" THEN cell.Font.Color = Green
IF keyword = "west" THEN cell.Font.Color = Orange
IF keyword = "central" THEN cell.Font.Color = Pink

There will definitely be more than 3 keywords so I will not be
Click to expand...
able to use Conditional Formatting.
 
Rachael,

Try Interior.Colorindex rather than Font.Colorindex.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top