Macro to hightlight row using conditional formatting

drew_1600

New Member
Joined
Sep 30, 2006
Messages
4
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()
[T1].Activate
With Range([T1], [T65536].End(xlUp)).EntireRow
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$T1=""yes"""
.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!

-Drew
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Drew

Why do you need code?

This should be possible through Format>Conditional Formatting...
 

drew_1600

New Member
Joined
Sep 30, 2006
Messages
4
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".

=IF(L8>=10000,"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!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Drew

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

What exactly do you want to do?
 

drew_1600

New Member
Joined
Sep 30, 2006
Messages
4

ADVERTISEMENT

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.

-Drew
 

Claymationator

Well-known Member
Joined
Sep 26, 2006
Messages
705
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! :)
 

drew_1600

New Member
Joined
Sep 30, 2006
Messages
4
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.
 

Forum statistics

Threads
1,137,197
Messages
5,680,108
Members
419,880
Latest member
suarezprado

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
Top