Conditional Formats .activate not working in Personal.xls

[dlsmith36]

I have created a code which runs perfectly in the standard module of the file but I want this code to be available for use in other books. When I place this code in my Personal.xls, it creates an error on the .Activate of the conditional formats. How would I go about fixing this? Also, I there any way that I can reduce the code for easier reading since the formatting is the same just different formulas to adhere to.

Here is the snippet of code I am referencing:

'Adds conditional formats to columns in red font for errors to check
'LastRow = Range("L" & Rows.Count).End(xlUp).Row

With Sheet1
        With .Range("D2:D" & LastRow)
            .Activate
            .FormatConditions.Add xlExpression, Formula1:="=LEN(D2)>=255"
            .FormatConditions(1).Interior.ColorIndex = 3
            .FormatConditions(1).Font.ColorIndex = 3
        End With
        With .Range("F2:F" & LastRow)
            .Activate
            .FormatConditions.Add xlExpression, Formula1:="=LEN(F2)>=255"
            .FormatConditions(1).Interior.ColorIndex = 3
            .FormatConditions(1).Font.ColorIndex = 3
        End With
        With .Range("H2:H" & LastRow)
            .Activate
            .FormatConditions.Add xlExpression, Formula1:="=LEN(H2)>=90"
            .FormatConditions(1).Interior.ColorIndex = 3
            .FormatConditions(1).Font.ColorIndex = 3
        End With
        With .Range("J2:J" & LastRow)
            .Activate
            .FormatConditions.Add xlExpression, Formula1:="=LEN(J2)>=700"
            .FormatConditions(1).Interior.ColorIndex = 3
            .FormatConditions(1).Font.ColorIndex = 3
        End With
    End With

Thank you once again!

[tehneXus]

Hi,

This:

With Sheet1

is the worksheet with Codename Sheet1 of Personal.xls, no other sheet and you cannot use codenames to refer to a sheet in a different workbook then the code is.
If you want to use it on the sheet you have activated use

With ActiveSheet

a shorted code:

Sub test()
    Dim aConditions: aConditions = Array(255, 255, 90, 700)
    Dim LastRow As Long: LastRow = 50   'testing
    Dim i As Integer, j As Integer
'
    With ActiveSheet
        j = 0
        For i = 4 To 10 Step 2
            With .Range(.Cells(2, i), .Cells(LastRow, i))
                .FormatConditions.Add xlExpression, Formula1:="=LEN(" & .Cells(1, 1).Address(False, False) & ")>=" & aConditions(j)
                .FormatConditions(1).Interior.ColorIndex = 3
                .FormatConditions(1).Font.ColorIndex = 3
            End With
            j = j + 1
        Next i
    End With
End Sub

[dlsmith36]

Thank you, I have been working on this all day and thought it was something just that simple. Time for a break!