VBA code for 5-rule Conditional Formating
On May 28, 1:40*pm, JW wrote:
On May 28, 1:18*pm, Antonio wrote:
Would anyone have a VAb code for a macro that changes the color of a cell
according to 5 different rules? A2 would be black if A1=1, green if A1=2,
gray if A1=3, blue if A1=4 or gold if A1=5.
Thanks
Here's one way. *Right click the sheet tab where you want this to
happen and select View Code. *Paste the below code in there. *Change
the Range("A2:A50") to whatever range you want the condition to apply.
Private Sub Worksheet_Change(ByVal Target As Range)
* * Dim clr As Integer
* * If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
* * * * Select Case Target
* * * * * * Case 1
* * * * * * * * clr = 1
* * * * * * Case 2
* * * * * * * * clr = 4
* * * * * * Case 3
* * * * * * * * clr = 15
* * * * * * Case 4
* * * * * * * * clr = 41
* * * * * * Case 5
* * * * * * * * clr = 44
* * * * * * Case Else
* * * * * * * * clr = -4142
* * * * End Select
* * * * Target.Offset(0, 1).Interior.ColorIndex = clr
* * End If
End Sub
typo on my part.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim clr As Integer
If Not Intersect(Target, Range("A2:A50")) Is Nothing Then
Select Case Target
Case 1
clr = 1
Case 2
clr = 4
Case 3
clr = 15
Case 4
clr = 41
Case 5
clr = 44
Case Else
clr = -4142
End Select
Target.Offset(0, 1).Interior.ColorIndex = clr
End If
End Sub
|