Highlight changes to a row whenever a particular cell is specify a value e.g. "Lost"

[clng]

Dear all,

Will have to trouble you guys for a simple macro. Took a search but could not find anything similar.

If I have a cell (e.g. c9) changed from "pending" to "lost", I would like the "whole" row (only cells with value and not the whole row, 256 columns) to be highlighted red and other colours if I change to other value (e.g. "win").

Thanks in advance.

Cheers,
CL

[broro183]

giday,

There's no need for a macro, in this case it can all be done with Conditional Formatting (check Help for further info).

Select the range (eg A9 & across & down as far as you that you want to change colour),
press [alt + o + d], choose "formula is" from the dropdown, paste "=NOT(AND(ISBLANK(A9),$C9="Lost"))" into the field on the right, click [format], [patterns] & choose red, click [ok] all the way out.

If it hasn't worked, press [alt + o + d] again & check the formula because sometimes Excel puts extra apostrophes around the whole formula, if so, remove them.

The above use of "$C9" assumes that each row will have the pending/lost/won in column C.


hth,
Rob

[clng]

Hi Rob,

Thank you for your help.

However, if we could use macro as a public sub, we could do this to all identical workbooks.

Do you think a macro will be better?

Thanks again.

Cheers,
CL

[broro183]

hi,

Yep, if you have multiple workbooks a macro probably would be better. Like you, I'm still learning but in this case (& others may have a better approach?), I'd still use the conditional formatting approach but would apply it via a macro b/c this means it will update as soon as the cell in the C column is changed.
Another approach would be to use a macro that checks for "Lost" & then colours the cells but this would need to be rerun repeatedly (by you) unless you used a worksheet change macro.
To see how these work do a search for "Sub Worksheet_Change(ByVal Target As Range)".

Are you used to using/developing macros?
I'll assume you are, but if not, check out:

To apply CF using a macro try the below - please note that I haven't really tested it so it may have some hidden glitches:
Check for line break issues & also the "warning" string I've included may not be necessary if you don't have other CF in your files.

Please Login or Register  to view this content.

Rob

[clng]

Rob,

You are very modest. Thanks for the help and I shall try it out soon and post back to see if it works.

Btw, I am also not very good with macro and still learning.

Cheers,
CL