changing the background color based on a cell value

BMD

Board Regular
Joined
Oct 5, 2005
Messages
211
I need to change the background color for c5:c29 based on the value (1 or 2) in cell c4 and if c8 is a (1 or 2) then change c9:c29 and if c18 is a (1 or 2) then change c19:c29 and this will happen maybe 100 different times on this worksheet.

I did something like this a long time ago but can get this to work now?

Thanks for any pointers.
BMD
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Try conditional formatting:
Your first example you can try...
Highlight C5:C29
in the menu bar >Format>ConditionalFormatting
Change CellValueIs to FormulaIs and enter =1 change color to green and do a second condition to =2 change color to orange.

Is this the idea?

Michael
 
Upvote 0
Michael
Thanks but I have about 50 or more, and Conditional Formatting has a limit of three. I know this can be done with VBA but I can't get it to work the way I need.
Thanks,
BMD
 
Upvote 0
So you need VBA. Maybe using Case in VBA to get what you like:
Something like:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

	If Not Intersect(Target, Range("A1:A10")) is Nothing Then
		Select Case Target
			Case 1 To 5
				icolor = 6
			Case 6 To 10
				icolor = 12
			Case 11 To 15
				icolor = 7
			Case 16 To 20
				icolor = 53
			Case 21 To 25
				icolor = 15
			Case 26 To 30
				icolor = 42
			Case Else
				'Whatever
		End Select
		
		Target.Interior.ColorIndex = icolor
	End If

End Sub
Michael
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top