P
Pam
Hi,
I have the following code to name a range supplied by Otto Moehrbach and I
have added code to conditionally format named range but am now receiving a
syntax error at this line:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(RIGHT(D2)<>"u",B2=1)"
Sub ColorDept()
Dim rColB As Range
Dim rFirst As Range
Dim rLast As Range
Set rColB = Range("B2", Range("B" & Rows.Count).End(xlUp))
Set rFirst = rColB.Find(What:="5", After:=rColB(rColB.Count),
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
Set rLast = rColB.Find(What:="1", After:=rColB(1), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
Range(rFirst, rLast).Name = "Dept1"
Range("Dept1").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(RIGHT(D2)<>"u",B2=1)"
Selection.FormatConditions(1).Interior.ColorIndex = 4
End Sub
Can someone please tell me what I'm doing wrong? Also, is there a way to
maybe case select this as I have Dept's 1-9 that I need to name and apply
slightly different code to each?
Any help is greatly appreciated.
Thanks in advance,
Pam
I have the following code to name a range supplied by Otto Moehrbach and I
have added code to conditionally format named range but am now receiving a
syntax error at this line:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(RIGHT(D2)<>"u",B2=1)"
Sub ColorDept()
Dim rColB As Range
Dim rFirst As Range
Dim rLast As Range
Set rColB = Range("B2", Range("B" & Rows.Count).End(xlUp))
Set rFirst = rColB.Find(What:="5", After:=rColB(rColB.Count),
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
Set rLast = rColB.Find(What:="1", After:=rColB(1), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
Range(rFirst, rLast).Name = "Dept1"
Range("Dept1").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(RIGHT(D2)<>"u",B2=1)"
Selection.FormatConditions(1).Interior.ColorIndex = 4
End Sub
Can someone please tell me what I'm doing wrong? Also, is there a way to
maybe case select this as I have Dept's 1-9 that I need to name and apply
slightly different code to each?
Any help is greatly appreciated.
Thanks in advance,
Pam