Hi,
I'm looking to add colour to a row selection when a certain criteria is
met. There is more than three criteria so the below Conditional
formatting won't work on the test table also attached.
Order customer source £
4 bill a 6
4 bill d 5
5 bill a 7
5 bill b 4
5 bill c 3
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF($C1=""a"",TRUE,FALSE)"
Selection.FormatConditions(1).Interior.ColorIndex = 36
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF($C1=""b"",TRUE,FALSE)"
Selection.FormatConditions(2).Interior.ColorIndex = 35
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF($C1=""f"",TRUE,FALSE)"
Selection.FormatConditions(3).Interior.ColorIndex = 40
So I've tried using a If and Do statement but I'm having trouble with
it. Can anyone point me in the right direction
Sub Add_Colour()
Range("C2").Select
Application.ScreenUpdating = False
Do
If ActiveCell = "a" Then Call SelectActiveRow
With Selection
..ColorIndex = 36
End With
End If
If ActiveCell = "b" Then Call SelectActiveRow
End
With Selection
..ColorIndex = 35
End With
End If
If ActiveCell = "c" Then Call SelectActiveRow
With Selection
..ColorIndex = 34
End With
End If
If ActiveCell = "d" Then Call SelectActiveRow
With Selection
..ColorIndex = 37
End With
End If
ActiveCell.Offset(1, 0).Range("A1").Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
Application.ScreenUpdating = True
End Sub
Sub SelectActiveRow()
If IsEmpty(ActiveCell) Then Exit Sub
' ignore error if activecell is in Column A
On Error Resume Next
If IsEmpty(ActiveCell.Offset(0, -1)) Then Set LeftCell = ActiveCell
Else Set LeftCell = ActiveCell.End(xlToLeft)
If IsEmpty(ActiveCell.Offset(0, 1)) Then Set RightCell = ActiveCell
Else Set RightCell = ActiveCell.End(xlToRight)
Range(LeftCell, RightCell).Select
End Sub
Thanks in advance
VBA Noob
--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile:
http://www.excelforum.com/member.php...o&userid=33833
View this thread:
http://www.excelforum.com/showthread...hreadid=536110