Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Cell Background Color when a keyword is entered
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Cell Background Color when a keyword is entered
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" wrote in message ... 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 = 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 able to use Conditional Formatting. Any help would be appreciated. Thanks. Rachael |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Cell Background Color when a keyword is entered
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" wrote in message ... 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 = 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 able to use Conditional Formatting. Any help would be appreciated. Thanks. Rachael |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Cell Background Color when a keyword is entered
Thank you Bob & To
How do I change the background color rather than the font color Rachae |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Cell Background Color when a keyword is entered
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) "Rachael" wrote in message ... Thank you Bob & Tom How do I change the background color rather than the font color? Rachael |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Cell Background Color when a keyword is entered
cell.Interior.ColorIndex =
-- Regards, Tom Ogilvy "Rachael" wrote in message ... Thank you Bob & Tom How do I change the background color rather than the font color? Rachael |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Cell Background Color when a keyword is entered
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" wrote in message ... 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 = 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 able to use Conditional Formatting. Any help would be appreciated. Thanks. Rachael |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Cell Background Color when a keyword is entered
Thanks very much everyone x
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Cell Background Color based on data from another cell | Excel Discussion (Misc queries) | |||
changing background color | Excel Discussion (Misc queries) | |||
Changing Cell Background Color Depending on the Number | Excel Discussion (Misc queries) | |||
changing cell background color | Excel Worksheet Functions | |||
Changing background color based on different cell | Excel Discussion (Misc queries) |