Count Conditional Formatting Colours - Excel 2003

[Chris Stammers]

Hello,

I have a VBA program in Excel 2003 that has been written for me to count
colours. It basically looks for the colour number and counts the number of
occurrences. However, it doesn't work for conditional formatting, as I assume
that the cell isn't actually coloured; it is merely displaying a colour. In
essence, the program is working when cells have actually been coloured. Is
there a way to get the program to recognise the colours in conditional
formatting? All of the variables and colour numbers have been declared in the
program already.

Many thanks.

Regards,
Chris

 

[Jarek Kujawa]

it is possible

to achieve that post your code pls

 

[Mike H]

Hi,

Look here, it ain't easy!!

http://www.xldynamic.com/source/xld.CFConditions.html
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Chris Stammers]

Hi Mike and Jarek,

Many thanks for your replies. Here is the code as it stands at the moment.
It is basically looking at different sections of a report, which is a monthly
report, and trying to count the conditional formatting colours, which I
obtained using some code I got from this forum. It is quite lengthy, so any
help you can offer will be much appreciated. It is still a work in progress!

Many thanks again,
Chris

Sub cntCols()



'Local variables
**************************************************************



Dim repDate As Integer

'Dim Monthno As Integer


Dim ranCel As Range

Dim intCol As Integer



Dim rowS As Integer

Dim rowE As Integer

Dim colS As Integer

Dim colE As Integer



Dim colm01 As Integer

Dim colm02 As Integer

Dim colm03 As Integer

Dim colm04 As Integer

Dim coly01 As Integer

Dim coly02 As Integer

Dim coly03 As Integer

Dim coly04 As Integer



'Reset variables
****************************************************************



colm01 = 0

colm02 = 0

colm03 = 0

colm04 = 0

coly01 = 0

coly02 = 0

coly03 = 0

coly04 = 0



Cells(2, 6) = 0

Cells(2, 7) = 0

Cells(2, 8) = 0

Cells(2, 9) = 0



'Identify month to retrieve
************************************************************



repDate = Sheets("Title Page").Cells(17, 11)

Sheets("Heat Map 2010").Select



Select Case repDate



Case 1
rowS1 = 14
rowE1 = 98
colS1 = 5
colE1 = 5

rowS2 = 107
rowE2 = 127
colS2 = 5
colE2 = 5

rowS3 = 136
rowE3 = 220
colS3 = 5
colE3 = 5

rowS4 = 229
rowE4 = 339
colS4 = 5
colE4 = 5

rowS5 = 348
rowE5 = 426
colS5 = 5
colE5 = 5

rowS6 = 449
rowE6 = 463
colS6 = 5
colE6 = 5

rowS7 = 492
rowE7 = 538
colS7 = 5
colE7 = 5


Case 2

rowS1 = 14
rowE1 = 98
colS1 = 7
colE1 = 7

rowS2 = 107
rowE2 = 127
colS2 = 7
colE2 = 7

rowS3 = 136
rowE3 = 220
colS3 = 7
colE3 = 7

rowS4 = 229
rowE4 = 339
colS4 = 7
colE4 = 7

rowS5 = 348
rowE5 = 426
colS5 = 7
colE5 = 7

rowS6 = 449
rowE6 = 463
colS6 = 7
colE6 = 7

rowS7 = 492
rowE7 = 538
colS7 = 7
colE7 = 7

Case 3

rowS1 = 14
rowE1 = 98
colS1 = 9
colE1 = 9

rowS2 = 107
rowE2 = 127
colS2 = 9
colE2 = 9

rowS3 = 136
rowE3 = 220
colS3 = 9
colE3 = 9

rowS4 = 229
rowE4 = 339
colS4 = 9
colE4 = 9

rowS5 = 348
rowE5 = 426
colS5 = 9
colE5 = 9

rowS6 = 449
rowE6 = 463
colS6 = 9
colE6 = 9

rowS7 = 492
rowE7 = 538
colS7 = 9
colE7 = 9

Case 4

rowS1 = 14
rowE1 = 98
colS1 = 11
colE1 = 11

rowS2 = 107
rowE2 = 127
colS2 = 11
colE2 = 11

rowS3 = 136
rowE3 = 220
colS3 = 11
colE3 = 11

rowS4 = 229
rowE4 = 339
colS4 = 11
colE4 = 11

rowS5 = 348
rowE5 = 426
colS5 = 11
colE5 = 11

rowS6 = 449
rowE6 = 463
colS6 = 11
colE6 = 11

rowS7 = 492
rowE7 = 538
colS7 = 11
colE7 = 11

Case 5

rowS1 = 14
rowE1 = 98
colS1 = 13
colE1 = 13

rowS2 = 107
rowE2 = 127
colS2 = 13
colE2 = 13

rowS3 = 136
rowE3 = 220
colS3 = 13
colE3 = 13

rowS4 = 229
rowE4 = 339
colS4 = 13
colE4 = 13

rowS5 = 348
rowE5 = 426
colS5 = 13
colE5 = 13

rowS6 = 449
rowE6 = 463
colS6 = 13
colE6 = 13

rowS7 = 492
rowE7 = 538
colS7 = 13
colE7 = 13

Case 6

rowS1 = 14
rowE1 = 98
colS1 = 15
colE1 = 15

rowS2 = 107
rowE2 = 127
colS2 = 15
colE2 = 15

rowS3 = 136
rowE3 = 220
colS3 = 15
colE3 = 15

rowS4 = 229
rowE4 = 339
colS4 = 15
colE4 = 15

rowS5 = 348
rowE5 = 426
colS5 = 15
colE5 = 15

rowS6 = 449
rowE6 = 463
colS6 = 15
colE6 = 15

rowS7 = 492
rowE7 = 538
colS7 = 15
colE7 = 15

Case 7

rowS1 = 14
rowE1 = 98
colS1 = 17
colE1 = 17

rowS2 = 107
rowE2 = 127
colS2 = 17
colE2 = 17

rowS3 = 136
rowE3 = 220
colS3 = 17
colE3 = 17

rowS4 = 229
rowE4 = 339
colS4 = 17
colE4 = 17

rowS5 = 348
rowE5 = 426
colS5 = 17
colE5 = 17

rowS6 = 449
rowE6 = 463
colS6 = 17
colE6 = 17

rowS7 = 492
rowE7 = 538
colS7 = 17
colE7 = 17

Case 8

rowS1 = 14
rowE1 = 98
colS1 = 19
colE1 = 19

rowS2 = 107
rowE2 = 127
colS2 = 19
colE2 = 19

rowS3 = 136
rowE3 = 220
colS3 = 19
colE3 = 19

rowS4 = 229
rowE4 = 339
colS4 = 19
colE4 = 19

rowS5 = 348
rowE5 = 426
colS5 = 19
colE5 = 19

rowS6 = 449
rowE6 = 463
colS6 = 19
colE6 = 19

rowS7 = 492
rowE7 = 538
colS7 = 19
colE7 = 19

Case 9

rowS1 = 14
rowE1 = 98
colS1 = 21
colE1 = 21

rowS2 = 107
rowE2 = 127
colS2 = 21
colE2 = 21

rowS3 = 136
rowE3 = 220
colS3 = 21
colE3 = 21

rowS4 = 229
rowE4 = 339
colS4 = 21
colE4 = 21

rowS5 = 348
rowE5 = 426
colS5 = 21
colE5 = 21

rowS6 = 449
rowE6 = 463
colS6 = 21
colE6 = 21

rowS7 = 492
rowE7 = 538
colS7 = 21
colE7 = 21

Case 10

rowS1 = 14
rowE1 = 98
colS1 = 23
colE1 = 23

rowS2 = 107
rowE2 = 127
colS2 = 23
colE2 = 23

rowS3 = 136
rowE3 = 220
colS3 = 23
colE3 = 23

rowS4 = 229
rowE4 = 339
colS4 = 23
colE4 = 23

rowS5 = 348
rowE5 = 426
colS5 = 23
colE5 = 23

rowS6 = 449
rowE6 = 463
colS6 = 23
colE6 = 23

rowS7 = 492
rowE7 = 538
colS7 = 23
colE7 = 23

Case 11

rowS1 = 14
rowE1 = 98
colS1 = 25
colE1 = 25

rowS2 = 107
rowE2 = 127
colS2 = 25
colE2 = 25

rowS3 = 136
rowE3 = 220
colS3 = 25
colE3 = 25

rowS4 = 229
rowE4 = 339
colS4 = 25
colE4 = 25

rowS5 = 348
rowE5 = 426
colS5 = 25
colE5 = 25

rowS6 = 449
rowE6 = 463
colS6 = 25
colE6 = 25

rowS7 = 492
rowE7 = 538
colS7 = 25
colE7 = 25

Case 12

rowS1 = 14
rowE1 = 98
colS1 = 27
colE1 = 27

rowS2 = 107
rowE2 = 127
colS2 = 27
colE2 = 27

rowS3 = 136
rowE3 = 220
colS3 = 27
colE3 = 27

rowS4 = 229
rowE4 = 339
colS4 = 27
colE4 = 27

rowS5 = 348
rowE5 = 426
colS5 = 27
colE5 = 27

rowS6 = 449
rowE6 = 463
colS6 = 27
colE6 = 27

rowS7 = 492
rowE7 = 538
colS7 = 27
colE7 = 27



End Select



'Loop for counting colours
********************************************************************



For Each ranCel In Sheets("Heat Map 2010").Range(Cells(rowS1, colS1),
Cells(rowE1, colE1))

'Sub ConditionalFormat()

With Sheets("Heat Map 2010").Range(Cells(rowS1, colS1), Cells(rowE1,
colE1))
MsgBox .FormatConditions(1).Formula1
MsgBox .FormatConditions(2).Formula1
MsgBox .FormatConditions(3).Formula1
MsgBox .FormatConditions(1).Interior.ColorIndex
MsgBox .FormatConditions(2).Interior.ColorIndex
MsgBox .FormatConditions(3).Interior.ColorIndex
End With

'End Sub


If ranCel.Interior.ColorIndex = 255 Then

col01 = col01 + 1

End If



If ranCel.Interior.ColorIndex = 39423 Then

col02 = col02 + 1

End If



If ranCel.Interior.ColorIndex = 65280 Then

col03 = col03 + 1

End If



If ranCel.Interior.ColorIndex = -4142 Then

col04 = col04 + 1

End If



Next ranCel



'Populate values ion specified targets
***********************************************



Cells(2, 6) = col01

Cells(2, 7) = col02

Cells(2, 8) = col03

Cells(2, 9) = col04



'Reset variables
****************************************************************



colm01 = 0

colm02 = 0

colm03 = 0

colm04 = 0

coly01 = 0

coly02 = 0

coly03 = 0

coly04 = 0



Sheets("Count Colours").Cells(5, 5).Select



End Sub

 

[Dave Peterson]

I wouldn't do this.

Instead, I'd use a formula in a different cell that mimics the rules used in the
conditional formatting.

Then I'd use those cells to do the counting--maybe even use them for the
conditional formatting of the original cells. It may make it easier to keep
them in sync.