conditional formatting 1004 errors

S

Sarah

I'm a rookie. I am getting Run-time error '1004': Unable to set the Bold
property of the Font class with the following code:

Range("I10:I1000").Select

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(AND(ABS($J10)>$G$5,ABS($I10)>$G$4))"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True

Thank you
 
R

RyanH

Is the sheet you are trying to apply the formats protected? If so, you need
to unprotect the worksheet first then reapply the protection.

Sub TEST()

' unprotect sheet first
Sheets("Sheet1").Unprotect Password:="password"

' your code
With Range("I10:I1000")
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(AND(ABS($J10)>$G$5,ABS($I10)>$G$4))"

.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
.FormatConditions(1).StopIfTrue = True
End With

' protect sheet
Sheets("Sheet1").Protect Password:="password"

End Sub

Hope this helps! If so, let me know by clicking YES below.
 
S

Sarah

No, it's not protected.

RyanH said:
Is the sheet you are trying to apply the formats protected? If so, you need
to unprotect the worksheet first then reapply the protection.

Sub TEST()

' unprotect sheet first
Sheets("Sheet1").Unprotect Password:="password"

' your code
With Range("I10:I1000")
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(AND(ABS($J10)>$G$5,ABS($I10)>$G$4))"

.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
.FormatConditions(1).StopIfTrue = True
End With

' protect sheet
Sheets("Sheet1").Protect Password:="password"

End Sub

Hope this helps! If so, let me know by clicking YES below.
 
S

Sarah

Interestingly, I have found if I change the conditional formatting to shade
the cells, the macro works. With the code exactly the same, except as follows:

With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0

Bug?
 
R

RyanH

Do you already have a format condition in that range? If so, I think you
will have to delete the old condition and apply the new one. For example:

Range("A1:A10").FormatConditions.Delete
Range("A1:A30").FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(AND(ABS($J10)>$G$5,ABS($I10)>$G$4))"

Hope this helps! If so, let me know by clicking "YES" below.
 
S

Sarah

You're right! That worked. My other reply had worked only because I had
deleted the existing conditional formatting. Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top