Count Conditionally Formatted Cells (Dynamic Range) | Excel VBA Macro

Опубликовано: 05 Октябрь 2024
на канале: greggowaffles

5,827

Excel VBA Macro: Count Conditionally Formatted Cells (Dynamic Range). In this video, we create code that automatically counts all conditionally formatted cells in a range, regardless of the number of rows. This code allows the user to easily count conditionally formatted cells across multiple columns with different criteria for each. We use a Message Box and Else If to account for there being zero, one, or more conditionally formatted cells in our count.

Data used in this video:
https://gsociology.icaap.org/datauplo...

This is a modification of code from:
https://www.excelsirji.com/vba-code-c...

Code:

Sub count_cond_cells()

Dim rng As Range
Dim rngCell As Range
Dim row_count As Integer
Dim cond_count As Integer
Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("Sheet1")

ws.Activate
row_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))
cond_count = 0

Set rng = ws.Range("D2:J" & row_count)

For Each rngCell In rng

If Cells(rngCell.Row, rngCell.Column).DisplayFormat. _
Interior.Color = RGB(255, 199, 206) Then
cond_count = cond_count + 1
End If

Next

If cond_count = 0 Then

MsgBox "There are no conditionally formatted cells."

ElseIf cond_count = 1 Then

MsgBox "There is 1 conditionally formatted cell."

Else
MsgBox "There are " & cond_count & " conditionally formatted cells."

End If

End Sub

#ExcelVBA #ExcelMacro