Macro button which checks for a value in certain range of cells per row?

Hi,

I'm looking to create a macro button which checks whether there is any value in a range of 3 cells in a row, and if there is, it then changes the background colour of another range of cells in the same row, if blank.

I can do this with conditional formatting, however I can only do one row at a time. I need a macro button which goes through every row and checks each one invidually.

For example (Please see attached sheet), if there is any value across the columns C, D or E per row, then the corresponding cells in Column A and B will go a red colour (only if individually they are blank). If there is already a value in Cell A2 for example, then it will stay normal. Basically I'm using this as a mandatory fields tool, whereby Columns A and B are mandatory and so if a user starts to enter values in each row, then it will recognise this from the non mandatory fields (C, D and E) and tell them to complete A and B.

If anyone can help me with this, that would be greatly appreciated.

Thanks,

Jake

Hi Jake
maybe conditional formatting (see attached file)

Does this help you any?

Please Login or Register  to view this content.

Thanks nilem. That is a really good way for conditional formatting. I appreciate that.

John, the code doesnt bring up any errors, but doesn't seem to be working though when I enter a value into those cells. Would I be able to use this in a button? I cant seem to assign it?

Originally Posted by Jake_Rex

Thanks nilem. That is a really good way for conditional formatting. I appreciate that.

John, the code doesnt bring up any errors, but doesn't seem to be working though when I enter a value into those cells. Would I be able to use this in a button? I cant seem to assign it?

Sorry about that. This code would not require a button. It goes in the Worksheet Module/Change Event. In the VBE click on the Sheet for the event to occur. On the right dropdown, right window select Worksheet, and left window select Change. The code would go there.

Thanks for the reply John. I've added the code but unfortunately there still seems to be nothing happening.

I tested it on the sample you provided. It worked as I thought you wanted. Maybe I'm misunderstanding what you want to happen.

Code goes here:

Developers Tab>Visual Basic Editor>Left side select the Project, double click on the Sheet number>On the right side will appear a window with two dropdowns. Left select Worksheet, right select Change.