I have 30 or so rows of data each with about 23 numbers in them. Each cell
in that range has conditional formating in it to see if it is over a target
number. I now need excel to count how man of them are above goal. I am
using colorindex 5 and have some code that will count how many as long as I
just color the cell with the fill icon but for some reason it does not want
to do it when I use CF. I was trying to get away from CF and just write some
loop in a macro that will look to see if it is over goal and then color it
blue, then move on to the next stat but I am not sure that excel can do that.
Any ideas on how I can do this?

OK I think I may be getting closer. I just figured out this much:

Sub MaxClose()
'This will do the cell color for Close
Dim i As Integer
i = 1
Do Until i > 30
If Range("H11") > Range("G11") Then
Range("H11").Select
With Selection.Interior
.ColorIndex = 5
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
End If
i = i + 1
Loop
End Sub

That will change the fill color of H11 and I think I have it close to
actually looping to its right 30 time. After it gets all of that then my
code to count fill color works great since it is not using CF. How to I do
the cell.offset so that it will start at h11 and move right = to i ?
"ElkySS" wrote:

> I have 30 or so rows of data each with about 23 numbers in them. Each cell
> in that range has conditional formating in it to see if it is over a target
> number. I now need excel to count how man of them are above goal. I am
> using colorindex 5 and have some code that will count how many as long as I
> just color the cell with the fill icon but for some reason it does not want
> to do it when I use CF. I was trying to get away from CF and just write some
> loop in a macro that will look to see if it is over goal and then color it
> blue, then move on to the next stat but I am not sure that excel can do that.
> Any ideas on how I can do this?

See Chip Pearson's site for a Function that can be used to count using CF Colors

http://www.cpearson.com/excel/CFColors.htm


Gord Dibben MS Excel MVP


On Sat, 30 Dec 2006 08:36:01 -0800, ElkySS <(E-Mail Removed)>
wrote:

>I have 30 or so rows of data each with about 23 numbers in them. Each cell
>in that range has conditional formating in it to see if it is over a target
>number. I now need excel to count how man of them are above goal. I am
>using colorindex 5 and have some code that will count how many as long as I
>just color the cell with the fill icon but for some reason it does not want
>to do it when I use CF. I was trying to get away from CF and just write some
>loop in a macro that will look to see if it is over goal and then color it
>blue, then move on to the next stat but I am not sure that excel can do that.
> Any ideas on how I can do this?

Using i as the column number looping to the right 30 columns:

Sub MaxClose()
'This will do the cell color for Close
Dim i As Integer
i = 8 'column H
For i = 8 To 38
If Cells(11, i) > Range("G11") Then
Cells(11, i).Interior.ColorIndex = 5
Cells(11, i).Font.ColorIndex = 2
End If
Next
End Sub

Mike F

"ElkySS" <(E-Mail Removed)> wrote in message
news:303B5535-499A-41AB-8E4F-(E-Mail Removed)...
> OK I think I may be getting closer. I just figured out this much:
>
> Sub MaxClose()
> 'This will do the cell color for Close
> Dim i As Integer
> i = 1
> Do Until i > 30
> If Range("H11") > Range("G11") Then
> Range("H11").Select
> With Selection.Interior
> .ColorIndex = 5
> .Pattern = xlSolid
> End With
> Selection.Font.ColorIndex = 2
> End If
> i = i + 1
> Loop
> End Sub
>
> That will change the fill color of H11 and I think I have it close to
> actually looping to its right 30 time. After it gets all of that then my
> code to count fill color works great since it is not using CF. How to I
> do
> the cell.offset so that it will start at h11 and move right = to i ?
> "ElkySS" wrote:
>
>> I have 30 or so rows of data each with about 23 numbers in them. Each
>> cell
>> in that range has conditional formating in it to see if it is over a
>> target
>> number. I now need excel to count how man of them are above goal. I am
>> using colorindex 5 and have some code that will count how many as long as
>> I
>> just color the cell with the fill icon but for some reason it does not
>> want
>> to do it when I use CF. I was trying to get away from CF and just write
>> some
>> loop in a macro that will look to see if it is over goal and then color
>> it
>> blue, then move on to the next stat but I am not sure that excel can do
>> that.
>> Any ideas on how I can do this?