Conditional formatting for Row and column

[MrActionHero]

Hi ,

I am very new to Excel and VBA programming .
My requirement is as follows
For a range of data in my excel ,
a) I want to highlight entire row in "Gray" Color if that row contain value "US" , "UK" in any cell in that perticular row . I also want to lock this row
b) I want to highlight entore column in "Gray" that containing a value "REVISED" in any cell in that column.

Note that I want to search entire range and then apply above conditional formatting .

I tried to search it on Goolgle and this forum .
But Since I am new to VBA I am not able to judge if a perticular code is useful for me

Could you pl help me out .

Regards
Amit

[Dulanic]

The conditional formatting is easy as that portion doesn't need VBA. Just highlight the row you want Normally you can just select has value, but that only highlights the cell with the value. Since you want to highlight the whole row use...

=COUNTIF(1:1,"US")+COUNTIF(1:1,"UK")

Change the 1:1 to whatever row your checking for these values. Or if you are putting it in multiple rows just start with the row # where the data is. For example, if you are adding the conditional formatting to rows 5-10, highlight 5-10, add the conditional formatting and the formula would be =COUNTIF(5:5,"US")+COUNTIF(5:5,"UK")

For columns, do the same just change the value. =COUNTIF(A:A,"REVISED")

The row lock will need to be sheet code for on change... Right click the sheet name, view code, and paste this:

Please Login or Register  to view this content.

It's not the pretiest code, I HATE goto, but I couldnt find a way to stop it from throwing the error when multiple cells were selected at once and goto next wouldnt work because it would just lock the row. I'm sure someone else can do it...better Keep in mind this needs to lock the whole sheet for the cell lock to take effect. Make sure to unlock all cells prior to using this or you will lock people from entering anything.