Auto color fill rows when condition met using a VBA or MACRO.

[enuff_2live]

I have been modifying a process sheet for my work, to stream line a few parts and make some of it automatic so it takes less time to create. I would like to have it highlight a range of cells in a row when one cell in that row meets a condition.

Basically, if D16=”X”, then B16 thru H16 will get highlighted. But if D16 equals anything else then the cells won’t get Highlighted. I want this for rows 16 thru 45. I tried Conditional Formatting on each row but that will take a long time to do every row. Each row needs to be independent of each other. There is also the possibility that rows will be copied and pasted from older versions so the conditional formatting can be over written. I have to leave the cells unlocked for data entry.

Is there a VBA code or MACRO that might steam line this?

There is a screen shot of what I would like it to look like basically.

[#2]

@enuff_2live With Conditional Formatting, create a rule:

=$B16="X"

applying to the entire range:

$B$16:$G$45

 A file with a working example is attached.

[#3]

@Riny_van_Eekelen That almost worked, except if you copy any of the cells the formatting gets changed. Is there a way to lock the formatting to the cells that I want to be formatted? Or is there a VBA code that might help in this instance? I need to make this as dummy proof as possible. I can't lock the cells do to the fact that the are were anyone can enter the data. Thank you for the help.