Jonathan has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Counting Cells with Condional Formats
I am trying to count the cells with a coloured conditional format. I tried doing the below to count the colour but it didn't work:
Function test(rRange As Range)
For Each CL In rRange
If CL.Interior.ColorIndex > 0 Then
test = test + 1
End If
Next CL
End Function
It only counted the coloured cells when they were not conditionally formatted. Any ideas? Many thanks.
RE: Counting Cells with Condional Formats
Hi Jonathan,
Sorry for the late reply! Our VBA trainers have been incredibly busy with training this week, but one of them will be in the office tomorrow. They will be able to reply to your query then.
Regards,
Andreas
Operation Co-ordinator
RE: Counting Cells with Condional Formats
Hi Jonathan,
Thank you for the forum question.
Excel does not see conditional formatted colours as colours. You will have to replicate the conditions in your code.
Lets say the range has the conditional rule >30000, then your code should be:
Function test(rRange As Range)
For Each CL In rRange
If CL.value > 30000 Then
test = test + 1
End If
Next CL
End Function
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
