Macro for conditional format (to avoid slowing down file)

Question

Hi all,&nbsp;I have a conditional format like this (also in attached file). It works great, but my file gets really slow because of it.&nbsp;&nbsp;Can I transfer this into a macro, so I can just press a button once a week, the new conditional format gets pulled and stored?&nbsp;HansVogelaar&nbsp;Could you help me with this?&nbsp;&nbsp;&nbsp;

hansvogelaar · Answer

Celia9&nbsp;
Try this:
Sub HighlightHolidays()
    Dim hol As Variant
    Dim emp As String
    Dim dtm As Date
    Dim i As Long
    Dim r As Long
    Dim c As Long
    Application.ScreenUpdating = False
    hol = Range("TableHolidays").Value
    For i = 1 To UBound(hol)
        dtm = hol(i, 2)
        r = Range("A:A").Find(What:=dtm, LookAt:=xlWhole).Row
        emp = hol(i, 1)
        c = Range("1:1").Find(What:=emp, LookAt:=xlWhole).Column
        Cells(r, c).Interior.Color = RGB(217, 217, 217)
    Next i
    Application.ScreenUpdating = True
End Sub

hansvogelaar · Answer

Celia9&nbsp;
Do you mean that you want to highlight the holidays by direct formatting instead of conditional formatting?

celia9 · Answer

Yes, I think I do. But it still should be based on the data like the conditional format is setup.

celia9 · Answer

HansVogelaar&nbsp;&nbsp;I just got to test it just now, it works, but when I transfer the code to my own file it stops working.The ranges are slightly different, I changed them in your posted file as well. The changes I have made work in your file, but not in mine. Could you help me out?&nbsp;The error I get: Object variable or With block variable not set&nbsp;Sub HighlightHoliday()Dim hol As VariantDim emp As StringDim dtm As DateDim i As LongDim r As LongDim c As LongApplication.ScreenUpdating = Falsehol = Range("TableHolidays").ValueFor i = 1 To UBound(hol)dtm = hol(i, 2)r = Range("G:G").Find(What:=dtm, LookAt:=xlWhole).Rowemp = hol(i, 1)c = Range("14:14").Find(What:=emp, LookAt:=xlWhole).ColumnCells(r, c).Interior.Color = RGB(217, 217, 217)Next iApplication.ScreenUpdating = TrueEnd Sub

hansvogelaar · Answer

Celia9&nbsp;
The macro runs without error when I try it, but Excel can be very finicky when you try to search for a date using VBA. Does this work for you?
Sub HighlightHolidays()
    Dim hol As Variant
    Dim emp As String
    Dim dtm As Date
    Dim i As Long
    Dim r As Long
    Dim r1 As Long
    Dim r2 As Long
    Dim c As Long
    Application.ScreenUpdating = False
    r1 = 15 ' row of first date
    r2 = Cells(Rows.Count, "G").End(xlUp).Row ' row of last date
    hol = Range("TableHolidays").Value
    For i = 1 To UBound(hol)
        dtm = hol(i, 2)
        For r = r1 To r2
            If Range("G" &amp; r).Value = dtm Then Exit For
        Next r
        emp = hol(i, 1)
        c = Range("14:14").Find(What:=emp, LookAt:=xlWhole).Column
        Cells(r, c).Interior.Color = RGB(217, 217, 217)
    Next i
    Application.ScreenUpdating = True
End Sub

Forum Discussion

Macro for conditional format (to avoid slowing down file)

5 Replies

Resources