Conditional formatting using VB and range

[PJ]

I'm trying to update an existing macro to add conditional formatting to a
range of cells. The current macro defines the range of cells to be updated
each time it runs as the range of cells is not always the same. The range of
cells is then named AttRange1.

I used the macro recorder to capture the basic formatting syntax but need
help adding it to the original macro. If the cell begins with the letter "E"
I need to change the font to red bold and this is what it gave me:

Range("J14").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEFT(J14)=""E"""
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
....two similar formatting conditions applied here...
Selection.Copy
Range("J14:Q35").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

In this case, the range begins in J14 and goes to Q35. I set the formatting
in J14 and then used the format painter to apply it to the rest of the range.
If my starting cell was always J14 I think I could get away with changing
the line after the copy command from "Range("J14:Q35").Select" to
"AttRange1.Select" but it is always different.

Is there anyway I can update the code in line 1 and 3 above to use my range
of cells AttRange1?

 

[JLGWhiz]

This should work if the range has been named at the
point that this snippet is used.


Dim c As Range
For Each c In Range("AttRange1")
If c.FormatConditions.Count < 1 Then
With c.FormatConditions
.Add Type:=xlExpression, _
Formula1:="=Left(" & c.Address & ", 1)=""E"""
With c.FormatConditions(1).Font
.Bold = True
.ColorIndex = 3
End With
End With
End If
Next

 

[PJ]

Thanks JLG. I'm out of the office today so I will test it out next week.

One follow up question though. I have two other conditions I want to add.
So after the last line in your code ("Next"), can I simply re-paste the same
code again for each of the two other conditions? I assume I can just update
the code and use "d" and "e" for the next two conditions along with updating
the formula line.

Thanks again.