Using VBA to alter cell background color

[Chr1sUK]

I am working on a spreadsheet, and where a cell is empty, I wish the background to be yellow, so that the user knows that he/she needs to enter data into it. When data is entered, the cell should return to white as normal.

Off the top off my head I tried...

If cells.text = " " THen
cells.text.background = vbyellow
Else
cells.text.background = vbwhite
End If

End Sub

I know I am missing something obvious, but it's been a while since I used VBA and I would like to use it in this instance.

Thanks.

PS - what sort of title should I be using for a sub in excel? would "Private Sub Yellow()" be ok or does it need to be specific to the sheet?

Thanks

 

[xlbo]

you can do this with conditional formatting - no need for code - if you do need to do it in code then you need to use the
interior.colorindex property of the range object

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Chr1sUK]

I have decided to use conditional formatting, I am here temporarily and nobody else knows VBA.

I only want the cells on a row to appear yellow if there is something entered into the first column of the row.

So when someone enters data into column A, a set range of cells in that row will turn yellow to alert the user that he/she must enter data.

I have tried conditional formatting, and I have tried an IF statement, but without much luck.

Suggestions?

Thanks

 

[xlbo]

Select ALL the cells you want to apply this to - starting in the top left corner of the area. I am assuming headers so this assumes your 1st cell selected is A2

In conditional formatting, change the 1st dropdown to "Formula Is" and enter

=len($A2)>0
set the background colour to yellow

et voila

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Chr1sUK]

What would I do without you Geoff!

Once again, thanks

 

[Chr1sUK]

That formula makes ALL the cells go yellow.

What I would like it to do is turn yellow when something is typed in column A AND when that partiular cell on that row is EMPTY. Once it has been filled, it should turn back to white.

 

[byundt]

Try using the AND function in your Conditional Formatting formula.
1) Select all the cells that will receive the Conditional Formatting
2) Open Conditional Formatting menu item and select Formula Is
3) Assuming that cell A1 is the top left cell in the selected range, enter the following formula:
=AND($A1<>"",A1="")
4) Click the Format button, then Patterns tab to choose your yellow highlight color
5) Click OK
If it doesn't work, then repeat the process and make sure that Excel didn't add any $ dollar signs or "quotes" in its zeal to be helpful. If you find any, remove them.

Owing to the logic of the formula, column A will never be highlighted, but columns B, C, etc. will be if there is an entry in column A, but none in a later column.