macro needed: change cell colour based on input in another column

Hi Guys

As the title suggests, I'm looking for a simple macro that will change the colour of the cell in column's O and P (row range 27:600) to "light yellow" if there is any data in the same row in column B. My brain isn't working today and I cant seem to make this work!

Any help would be much appreciated!

Many thanks for the swift response... although this doesn't seem to do anything! Which was the same problem I had as well...

Please Login or Register  to view this content.

I cant see why this isnt working, its driving me bonkers!!

Also, to complicate things... Im looking at amend the macro to remove the cell colour (default back to white) if any text is entered into the cells in columns O and P. Is this feasible?

weesteev,

You don´t need a macro to do this, just use conditional formating.

Last row is based on data in column A. You got data in that one?

Originally Posted by rkey

Last row is based on data in column A. You got data in that one?

Have changed to B (where the data is located) and still not working

Originally Posted by Pulsus

weesteev,

You don´t need a macro to do this, just use conditional formating.

Conditional formatting will only work in the targeted cell, I'm looking to adjust the colour of 2 cells based on the value in a different cell.

Where are you putting the code? It needs to go in a module. Not the particular worksheet. I just ran the code on a test workbook here and it worked fine.

Ok I have sussed out the problem! the code does indeed work but you need to manually run the macro. What im looking for is a VB script that will automatically run and detect input in column B, basically performing the function of conditional formatting. Any ideas?

Automatically is a bit harder. You can't just hotkey the macro?

http://kb.iu.edu/data/ahbm.html

weesteev,

Select the cell O27.

On conditional formating select "NEW RULE", then select the last option (Use a formula to ...), and insert this formula:

=IF(B27<>"";1;0)

After this Click in the "FORMAT BUTTON" behind and select the color you want.

Repeat the same process on cell P27.

So just use the format brush to all the cells you need that Rule.