Color cell on the basis of value present in the cell using macro.

[ThrottleWorks]

I have value in cell a1, value will be between 1 to 5.

I need to color cell b1 on the basis of value in the cell a1.

If the number is not between 1 to 5, I want to populate “Please check the value” in cell b1,


There are 5 different colors for each value.


P.S. - I can not use conditional formatting.


Can anyone help me in this please.

 

[Luke M]

This should at least help you get started.

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyValue As Variant

MyValue = Range("A1").Value
Application.EnableEvents = False
'Reset B1
Range("B1").Interior.Color = xlNone
Range("B1").ClearContents

Select Case MyValue
Case 1
Range("B1").Interior.ColorIndex = 2
Case 2
Range("B1").Interior.ColorIndex = 3
Case 3
Range("B1").Interior.ColorIndex = 4
Case 4
Range("B1").Interior.ColorIndex = 5
Case 5
Range("B1").Interior.ColorIndex = 6
Case Else
Range("b1").Value = "Please check the value"
End Select
Application.EnableEvents = True
End Sub

[/pre]

 

[ThrottleWorks]

Luke Sir, thanks a lot for the help.


Have a nice day, this will certainly help me.