Can I change the colour of my entire worksheet based on the value of one cell?

[mattpearce]

I have a spreadsheet that is being used as a user form. In one of the drop menus the user has the option to set the form as 'Draft' or 'Original'.

I would like to be able to tell my entire worksheet to change colour if the cell is set to 'Draft', making it obvious to the user that the form is not an original.

I have trawled through Q&A's trying to find an answer and can only find reference to Conditional Formatting, but in Excel 2010 I cannot find a way to extend the formatting to the entire sheet, just to rows or columns.

Any help would be appreciated.

[stnkynts]

Assuming drop down is cell A1

Please Login or Register  to view this content.

[mattpearce]

Thank you very much for the help, but it doesnt seem to be working (I am a VBA novice though, so its not surprising).

Please advise if Im expecting the wrong thing here, but I expect that when I make the selection from the drop box that the sheet changes colour at that point (ie no buttons, keyboard shortcuts etc).

My Draft and Original has been changed as I now have 4 selections (Pre Completion, Pre Completion Revised, Post Completion and Post Completion Revised) all in a drop box (C20), I need the sheet to change colour if either of the "Pre Completion" selections are made. I have tweaked your script slightly (to match my sheet) as below and pasted it to the foot of my existing VBA for this workbook:

Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("C20").Address Then
Select Case UCase(Target.Value)
Case Is = "POST COMPLETION"
Cells.Interior.ColorIndex = xlNone
Case Is = "POST COMPLETION REVISED"
Cells.Interior.ColorIndex = xlNone
Case Is = "PRE COMPLETION"
Cells.Interior.ColorIndex = 6
Case Is = "PRE COMPLETION REVISED"
Cells.Interior.ColorIndex = 6
End Select
End If

End Sub

[JOHN H. DAVIS]

Another:

Please Login or Register  to view this content.

[mattpearce]

John, thank you very much for your help.

Can I just check how I am supposed to introduce your script to my VBA?

I currently have a number of scripts set up in the "this workbook" section as this is a document I am sharing with users outside of my business (so want the scripts to follow the document as it is emailed etc) and thought I would need to just paste your script below it (this is all I have done for the 3 scripts I currently have in the sheet and they all just work dynamically as I change selections in the sheet), but when I paste your script to the foot of the sheet and change C20 to either of the "pre" options, nothing happens.

I presume its just me introducing your code to Excel/VBA incorrectly, but if you could point me in the right direction it would be greatly appreciated and I am sure it will work.

[Richard Buttrey]

Hi,

Assuming A1 contains the word draft then use the CF

Please Login or Register  to view this content.

set the format and in the Apply To box enter

Please Login or Register  to view this content.

where Z is a column that is completely empty and 1:1 is a row that's completely empty.

[JOHN H. DAVIS]

Instead of clicking on ThisWorkbook, in the VBE click on the Sheet you want the events to occur (ie right above ThisWorkbook). A drop-down window will appear on the right hand side. On the left select worksheet. On the right select Change. The code should be pasted there.

[stnkynts]

Your code should work if you put it in the correct spot. See John's post above

Please Login or Register  to view this content.