Macro to Conditionally Format Cells based on another cells value

Hi,

I have a series of worksheets which have in column A7:A1000 a drop down menu that allows the user to select the option of "No". When this happens the entire row (eg. A7:KX7) is then highlighted a different colour via conditional formatting. The formula for Conditional Format is: =ISNUMBER(SEARCH("No",$A7)) and applies to =$A$7:$KX$1000

Column B (B7:B1000) contains a unique ID number.

What id like to have is a macro that checks column A in each worksheet for a "No" value. When it finds one (say in A7) it would then search all the other worksheets for the ID number in the cell next to it (eg. B7). It would then highlight each row as per the conditional format above. This basically saver the user having to enter "No" against each ID number in every sheet - as it could occur many times.

Could anyone help, would be most appreciated.

Thanks

Hi,

The file is large and cant be attached here, so it's in my Dropbox public account, here: https://dl.dropboxusercontent.com/u/...0TRACKING.xlsm

Hope this works

Bump, please help

To hopefully simplify the request....

I want a macro to search Column A (A7:A1000) in each worksheet for the value "no". Every time it finds one it should look at the value of the cell in the adjacent Column B (B7:B1000) and then conditional format every row in each worksheet where this newly found value occurs (formatting is: B7:KX7 etc..)

Anyone? :-(

*sniff* Anyone, help would be most appreciated

Hi,

Thanks for your help, much appreciated. I cant get it to replicate what already exists in the example you uploaded. When I enter "No" and run the macro it doesnt do anything Also if i remove "No" from the ones highlighted and then run the macro, it doesn't remove the highlighting.

Hi,

I removed the conditional formatting before I tried it, still couldn't get it to work

Ive copied the code into a new module in my original sheet (and removed Cond Format) and run it from there, still nothing

Hi,

It's telling me it's too big to upload, which is weird as it's no bigger than the one you uploaded! Here it is from my Dropbox account:

Link

URL: https://dl.dropboxusercontent.com/u/...ST%20TEST.xlsm