Macro to hightlight row using conditional formatting


New Member
Sep 30, 2006
Hey guys, I'm trying to set up a macro using conditional formatting so
that an entire row of a worksheet will be highlighted if a particular
column's cell has the word "yes" in it.

For example, if there are any cells in column T that have the word
"yes" in them, I'd like Excel to automatically highlight that row
yellow. I found a solution online, but I need help tweaking it.
Here's what I've got:

Sub Highlight()
With Range([T1], [T65536].End(xlUp)).EntireRow
.FormatConditions.Add Type:=xlExpression, Formula1:= _
.FormatConditions(1).Interior.ColorIndex = 6
End With
End Sub

I'd like to change the range function to search every column in the
worksheet, instead of just column T. I just don't know enough about
VBA to do this.

Also, let's say I wanted to search three columns, like R, S, and T,
instead of every single column, how would the macro be written? Thanks
in advance!


Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Why do you need code?

This should be possible through Format>Conditional Formatting...
Upvote 0
Well, the next step after I get this working will be to incorporate a formula into the maco that will auto-populate the column with the word "yes" based on certain criteria--and then highlight each of the rows yellow.

For example, I'm currently using a formula similar to this one to determine whether the column needs the word "yes".


So, in this example, if cell L8 is greater than or equal to 10000, it will mark the cell that it's referring to with the word "yes".

I know something like this can be done, I'm just not sure how difficult it will be. Mainly because the spreadsheets I'm working with won't always be using 10000 as the value and the cell (L8 in this case) won't always necessarily be in column L.

Is there a way to have Excel ask which column's data I'm interested in, and then ask where it would like me to put the data, and finally ask me what value I'd like to use?

I know this is starting to get a little more complex, but I'd really like to be able to learn how to do this. If anything, I'd at least like a macro for the shading of the rows in the worksheet. Thanks again for the help!
Upvote 0

I'm sorry but I'm not following what you mean.

What exactly do you want to do?
Upvote 0
Well, for now I just need the code that I first pasted to be able to search every column for the word "yes"--and not just column T.

As an alternative I'd also like to know how I would specify multiple columns (such as T, U, and V).

I tried playing around with the Range function, but I can't seem to figure out how I would re-write that part. Maybe after I figure this part out I'll repost the other stuff I'm interested in doing. Thanks again.

Upvote 0
I think that there is an easier way to do this without having to use code. I actually use this in a weekly report for work. Below is how I have done it.

For example, let's say that I want to highlight C1:C25, E1:E25, and G1:G25 when A1="Yes."

First select the range of cells that you want highlighted (C1:C25, E1:E25, and G1:G25) and then with them highlighted go to "Format" and select "Conditional Formatting" from the list. With that box up change the first drop down from "Cell Value Is" to "Formula Is." Then in the next field put in the formula =$A$1="Yes". From there select the type of formatting you want (text, pattern, etc) and press OK.

To test it you can just type in "Yes" into A1 and it should automatically change the formatting for those specific columns that you have selected.

You can also add additional formatting to those cells if A1 were to say something different. Just do the same thing but add another condition.

Hope that helps! :)
Upvote 0
That's true. I guess the problem is that I'd rather not have to do any selecting at all. Most of the time I'm working with about 30 worksheets each consisting of about 20 rows that need highlighting. If there's a yes anywhere on the worksheet, I'd like to run a macro that highlights the entire row.

I just can't seem to figure out how to re-word the range part of the code to include every cell in the worksheet.
Upvote 0

Forum statistics

Latest member

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
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 "".
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