CODEWITHSUNDEEP
excelvbafor-loopconditional-formatting
nilss
nilss

Reputation: 3

Issue with macro relating to conditional formatting in Excel VBA

I'm trying to check sheets from bottom up for colors. If one is found I want to change the color of the relating sheet tab.

I have two problems and one issue I would like to get rid off.

  1. RGB(230, 184, 183) is a priority for me. I need to circle the sheet and check for it before I check for the rest. To clumsy to do this atm....

  2. Conditional formatting colors are not recognized. As far as I've seen I need another attempt for that?

Issue: I'm force starting at 1Row = 1000. Is there a better way?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim lRow As Long
Dim iCntr As Long
Dim ws As Worksheet
lRow = 1000
    For Each ws In ThisWorkbook.Worksheets
        For iCntr = lRow To 1 Step -1
            If ws.Cells(iCntr, 1).Interior.Color = RGB(230, 184, 183) Then
            ws.Tab.Color = RGB(230, 184, 183)
            Exit For
                ElseIf ws.Cells(iCntr, 1).Interior.Color = RGB(184, 204, 228) Then
                ws.Tab.Color = RGB(184, 204, 228)
                Exit For
                    Else: ws.Tab.Color = RGB(195, 215, 155)
            End If
       Next
   Next ws
End Sub

Upvotes: 0

Views: 331

Answers (2)

Cool Blue
Cool Blue

Reputation: 6476

The conditional format color is available through the DisplayFormat property of the Range object. For example... Ws.Cells(iCntr,1).DisplayFormat.Interior.Color

Upvotes: 1

Mark Moore
Mark Moore

Reputation: 510

I have had similar problems with conditionally formatted cells not being recognised by VBA and never found a fix. My solution in the end was not to use conditional formatting, but instead to colour the cells via VBA during a validation routine. This probably isn't what you want, I appreciate that.

With regard to you force starting at row 1000 and working upwards, there are several ways to find the last row of a sheet 1. Use the usedrange.rows value

For iCntr = ws.usedrange.rows.count To 1 Step -1

However depending on the structure of your sheet, Usedrange can be unpredictable (for example if your first row and first column are blank, it wont include those in the used range address) but depending on your circumstances, this may be the simplest way to do it. 2. Find the last populated row in a column you will be populated all the time, and use that as your counter

lRow = ws.Cells(.Rows.Count, "A").End(xlUp).Row

and keep the rest of your code the same

Upvotes: 0

Related Questions