Color Entire Row Based On A Value In A Cell

lonely

Board Regular
Joined
Feb 28, 2003
Messages
230
Hi All,
I am presently working on an excel worksheet which has numerous rows. I need to see each cell in column B and if i find the word "Red" in the cell, i need to change the color of the entire row to Red.

I tried to use conditional formatting for the purpose, however, it didnt work. I guess, conditional formatting works only when the entire cell value is "Red". In my case, the cell value may be something like "I Like Red, Blue and Green Color", or it can also be "Red is my favorite color".

Please can someone help me design a macro for the purpose.
Thanks,
Lonely
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Conditional formatting does work with that, you just have to use formulas instead of values:

Select the entire row that you wish to apply conditional formatting, to, let's say is row 2.

Go to Format | Conditional formatting...

Change "Values Is" to "Formula Is" and put this formula

=ISNUMBER(SEARCH("red",$B2))

note the relative reference in B2, that is so each cell in row 2 will look at column B to see if it should change the color or not.

Now click on the pattern button and change the format as you wish.

That's it !
 
Upvote 0
Hi Juan,
Thanks for taking time off to answer the problem. However, i was just thinking that it would be really very tricky to tell this to my team members who are not that tech savy.

Can we do it with a macro.. so that anyone can just run the macro and zoom... the work is done.

anyways,... thanks again,
Lonely
 
Upvote 0
Record a macro folowing the steps given to you by Juan Pablo and this will work. To record a macro Tools - Macro - Record New Macro
 
Upvote 0
Hi Wookie,
I tried to record the macro and use it for the purpose, and esited it. Finally i have something like:
**********
Sub formattingmacro()
Cells.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ISNUMBER(SEARCH(""t"",$A1))"
Selection.FormatConditions(1).Interior.ColorIndex = 33
Range("A1").Select
End Sub
**********
However, it does not work correctly. It applies the formatting to the cells which dont have the value "t" also.

Please can you suggest something.

Thanks,
Lonely
 
Upvote 0
This works for me.

Cells.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ISNUMBER(SEARCH(""red"",$B1))"
Selection.FormatConditions(1).Interior.ColorIndex = 17
 
Upvote 0
Hi,

What if I want to search the whole sheet....., and if it found the word 'red', example in row 26 (regardless of what column), it will color only A26 to D26 ? What will the vb script be like ?

:rolleyes:

Best Regards,
Ricky
 
Upvote 0
Try this:

<font face=Courier New>
<SPAN style="color:#00007F">Sub</SPAN> DoIt()
    <SPAN style="color:#00007F">With</SPAN> Range("A:D").FormatConditions
        .Delete
        Range("A1").Activate    <SPAN style="color:#007F00">'Just in case</SPAN>
        .Add Type:=xlExpression, Formula1:="=ISNUMBER(MATCH(""*red*"",1:1,0))"
        
        .Item(1).Interior.ColorIndex = 17
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
 
Upvote 0
Hi Juan,
Thanks for the message. The code worked well. Just as expected :)

Now i am trying to give mt yeam members more control over the formatting. I am looking at adding a form which asks the user to specify the string he is looking at. Then he can also specify the color he wants the rows to be colored with.

Can this be done... am i asking for something impossible. Anyway, the code till now works absolutely fantastic.
Thanks again for all your help,
Lonely
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top