Excel Macro - Color Cells Based on Content?

Question

I need to create an Excel Macro that colors rows a certain color based on a word in column c of the row. The two words are "add" and "remove". If the word "add" is present I would like the macro to color the row one color and vice versa with the word "remove". Either code or a method to do this while recording a macro would be fine.

Answer 1

Look in the help file for "conditional formatting". This should give you plenty to work with, also do a search on "conditional formatting excel" on your preferred search engine.

Answer 2

Excel Macro Cell Color

Answer 3

EDIT: here is working code to do exactly what you asked, i'm sure alot of people will find it crude but hey i'm entirely self taught and it works so who cares?



Sub Macro1()

On Error Resume Next

Dim current As String



For i = 1 To 65536 ' go from first cell to last



current = "c" & i ' cell counter



Range(current).Select ' visit the current cell



If Range(current).Text = "add" Then ' if it says add then we...

With Selection.Interior

.ColorIndex = 3 ' ...go red

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If



If Range(current).Text = "remove" Then ' if it says remove then we...

With Selection.Interior

.ColorIndex = 4 ' ...go green

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

Next i ' loop and check the next cell

End Sub



you could obviously build in extra 'if' statements for all kinds of stuff, or extra code to do more stuff with the cells containing the words you're looking for but the above covers what you were asking for - macro code to change cell colours depending on their contents.



although the code i wrote for you above works well, i personally think that the post above is a much crisper way of doing things because it doesn't rely on the user having macros enabled and having to run a macro.



but if it *was* a macro you were after then i hope this helped, and is worthy of the 10 points for best answer ;)

Answer 4

Are "add" and "remove" your only choices?



If = "add" then = "green"



Use the above logic for "remove"



{EDIT} The other poster's reference is excellent - incorporate that logic into your macro because the reference only refers to one cell and the same example was repeated for the two other cells in the reference.



Hope this helped.