Getting Runtime Error 1004 when Coding Conditional Formats with VBA

[gpratt]

Heya,

I'm working on some code to highlight the ActiveCell's row and column by using the Worksheet_SelectionChange() sub using conditional formatting, but I've run into some snags when I set any of the font properties for the second conditional format.

After lots of trial and error, I seem to have narrowed down the problem, but it doesn't help me figure out how to solve it. When I run the code below, I get either "Runtime error 1004: Unable to Set the Bold property of the Font class" or "Runtime error 1004: Application-definied or object-defined error" when I get to the .Font.Bold=True line under With ActiveCell.EntireRow line.

Sub ChangingFontColumnAndRow()

    Cells.FormatConditions.Delete
   
    ActiveCell.EntireColumn.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
    ActiveCell.EntireColumn.FormatConditions(ActiveCell.EntireColumn.FormatConditions.Count).SetFirstPriority
    With ActiveCell.EntireColumn.FormatConditions(1)
        .Interior.Color = 16304054
        .Font.Color = -65536
    End With
    ActiveCell.EntireColumn.FormatConditions(1).StopIfTrue = False
   
    ActiveCell.EntireRow.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
    ActiveCell.EntireRow.FormatConditions(ActiveCell.EntireRow.FormatConditions.Count).SetFirstPriority
    ActiveCell.EntireRow.FormatConditions(1).StopIfTrue = False
    With ActiveCell.EntireRow.FormatConditions(1)
        .Interior.Color = 16304054
        .Font.Bold = True
    End With
   
End Sub

I've tried setting other Font properties and they all give me similar errors. It obviously works fine if I'm working with the Interior property to change the fill color, so it does seem to be related to the Font class under the FormatConditions. However, it works fine with the first conditional format. Also, I found out that setting the font property on the second conditional format works fine if it's the same range as the first conditional format. (e.g. Using EntireColumn for both of them instead of EntireRow for the second conditional format.) So it also seems to be related to working with two different ranges in my code.

Obviously, since I'm planning on putting this in my Worksheet_SelectionChange() sub, I can't exactly change my selection in my code else I'll end calling the function and getting into an infinite loop. (Why yes, I might know this bit from my own experiences in solving this, LOL.) I also know I can do the highlighting with using regular formatting instead of conditional formatting, but then I'd have to delete all the formatting in the sheet each time I select a different cell and I don't necessarily want to do that with my spreadsheet.

I'm using Excel 2007 and I tried it on both a Windows XP and a Windows 7 computer and I get the Runtime error in the same place with both machines. Anyone seen anything like this? Any ideas I might be able to try to make this work?

Thanks, I really appreciate any help I get!

 

[paulosemblano]

ActiveCell.EntireRow.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
ActiveCell.EntireRow.FormatConditions(ActiveCell.EntireRow.FormatConditions.Count).SetFirstPriority
ActiveCell.EntireRow.FormatConditions(1).StopIfTrue = False
With ActiveCell.EntireRow.FormatConditions(1)
.Interior.Color = 16304054
.Font.Bold = True
End With

Por:

Dim I As String
I = Chr(ActiveCell.Column + 64) & ActiveCell.Row
Cells.FormatConditions.Delete
Rows(ActiveCell.Row & ":" & ActiveCell.Row).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.Interior.Color = 16304054
.Font.Bold = True
End With
Selection.FormatConditions(1).StopIfTrue = False
Range(I).Select

 

[gpratt]

Well, as I said, the end result is to put it in my Worksheet_SelectionChange() sub so I can't actually code in anything that actually changes the selection as then it'll call itself again and I'll end up in an infinite loop. I'm just working with a regular macro for testing purposes. Thanks though!

 

[paulosemblano]

infinite loop:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False

'code here

Application.EnableEvents = True
End Sub

 

[Norie]

Why not use the code in the worksheet event to do the formatting rather than setting the conditional formatting?

 

[gpratt]

infinite loop:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False

'code here

Application.EnableEvents = True
End Sub

Oh, that bit's handy! I'll definitely remember that, thank you much!

However, when I try to use the code you suggested in your first post, it still gives me the Runtime error at the same point so I'm right back where I started. Now, you did have a Cells.FormatConditions.Delete line at the beginning of the code which when left in, does make it work without errors. However, it deletes the conditional formatting I already set up for the column of the Active Cell, so I had to removed it and thus, I get the error. Anyway, here's my subroutine using your code so if you can see anything else I missing:

Sub ChangingFontColumnAndRow2()

    Cells.FormatConditions.Delete
    
    Dim I As String
   
    ActiveCell.EntireColumn.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
    ActiveCell.EntireColumn.FormatConditions(ActiveCell.EntireColumn.FormatConditions.Count).SetFirstPriority
    With ActiveCell.EntireColumn.FormatConditions(1)
        .Interior.Color = 16304054
        .Font.Color = -65536
    End With
    ActiveCell.EntireColumn.FormatConditions(1).StopIfTrue = False
   

    I = Chr(ActiveCell.Column + 64) & ActiveCell.Row
    Rows(ActiveCell.Row & ":" & ActiveCell.Row).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .Interior.Color = 16304054
        .Font.Bold = True
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range(I).Select
End Sub

 

[gpratt]

Why not use the code in the worksheet event to do the formatting rather than setting the conditional formatting?

Well, it's certainly one way to do it, but then I couldn't do any regular formatting outside of the highlighting I'm doing with each SelectionChange. And the worksheet I want to put this in already has a lot of formatting in it that would cause all sorts of problems to lose. So, I thought conditional formatting would make more sense since then I can delete all of it and it won't affect the data I already have or the worksheet. Plus, while it'll override the formatting of a cell when it's in a row or column that's selected, when I select something else, that cell will go back to the original formatting.