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
"Mike H" wrote:
> 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" wrote:
>
> > 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
Similar Threads
