Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cout CF in range that = blue
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cout CF in range that = blue
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cout CF in range that = blue
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 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cout CF in range that = blue
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" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why is range outlined in blue? | Excel Discussion (Misc queries) | |||
Condition BLUE | Excel Discussion (Misc queries) | |||
Missing Blue Border in xml mapped range | Excel Discussion (Misc queries) | |||
Range names are displaying in big blue letters on sheet | Excel Discussion (Misc queries) |