Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Cells with Conditional Formatting
Is there a way to look at a row of data that is either highlighted yellow or
red and to count only the data that is highlighted yellow and give me that number? None of my data is numerical....just one letter data, i.e.: "S" or "U" I just want to count how many of the cells are highlighted yellow in a specific row, if that makes more sense. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Cells with Conditional Formatting
Here's a UDF (Put in a Standard module)
and use it in cell A1, like =CountByColor(A8:H8,6) << where 6 (at present) = Yellow = change to =CountByColor(A8:H8,3) to get the reds HTH Function CountByColor(InRange As Range, WhatColorIndex As Integer) As Long ' Dim c As Range CountByColor = 0 Application.Volatile True For Each c In InRange.Cells If c.Interior.ColorIndex = WhatColorIndex Then TCount = TCount + 1 End If Next c CountByColor = TCount End Function "JasonC" wrote: Is there a way to look at a row of data that is either highlighted yellow or red and to count only the data that is highlighted yellow and give me that number? None of my data is numerical....just one letter data, i.e.: "S" or "U" I just want to count how many of the cells are highlighted yellow in a specific row, if that makes more sense. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Cells with Conditional Formatting
Jason:
I just noticed that my solution is not based on the use of conditional formatting, but rather based on your manually painting the cells. I now realize that you are using CF to get the colors red or yellow. Can you provide me a sample of your conditional formatting settings? Meanwhile I'll see if I can correct and resubmit what you want. Jim "JasonC" wrote: Jim, I can't seem to get it to work. I have the file with me. Could I send it to you and you set it up for me? JasonC Thanks, JasonC "Jim May" wrote: Here's a UDF (Put in a Standard module) and use it in cell A1, like =CountByColor(A8:H8,6) << where 6 (at present) = Yellow = change to =CountByColor(A8:H8,3) to get the reds HTH Function CountByColor(InRange As Range, WhatColorIndex As Integer) As Long ' Dim c As Range CountByColor = 0 Application.Volatile True For Each c In InRange.Cells If c.Interior.ColorIndex = WhatColorIndex Then TCount = TCount + 1 End If Next c CountByColor = TCount End Function "JasonC" wrote: Is there a way to look at a row of data that is either highlighted yellow or red and to count only the data that is highlighted yellow and give me that number? None of my data is numerical....just one letter data, i.e.: "S" or "U" I just want to count how many of the cells are highlighted yellow in a specific row, if that makes more sense. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Cells with Conditional Formatting
My Conditional Formatting goes like this:
Condition 1: Cell Value is Equal to: =B$36 Format with Yellow Cell Shading Condition 2: Cell Value is - Not Equal to: =B$36 Format with Red Cell Shading "Jim May" wrote: Jason: I just noticed that my solution is not based on the use of conditional formatting, but rather based on your manually painting the cells. I now realize that you are using CF to get the colors red or yellow. Can you provide me a sample of your conditional formatting settings? Meanwhile I'll see if I can correct and resubmit what you want. Jim "JasonC" wrote: Jim, I can't seem to get it to work. I have the file with me. Could I send it to you and you set it up for me? JasonC Thanks, JasonC "Jim May" wrote: Here's a UDF (Put in a Standard module) and use it in cell A1, like =CountByColor(A8:H8,6) << where 6 (at present) = Yellow = change to =CountByColor(A8:H8,3) to get the reds HTH Function CountByColor(InRange As Range, WhatColorIndex As Integer) As Long ' Dim c As Range CountByColor = 0 Application.Volatile True For Each c In InRange.Cells If c.Interior.ColorIndex = WhatColorIndex Then TCount = TCount + 1 End If Next c CountByColor = TCount End Function "JasonC" wrote: Is there a way to look at a row of data that is either highlighted yellow or red and to count only the data that is highlighted yellow and give me that number? None of my data is numerical....just one letter data, i.e.: "S" or "U" I just want to count how many of the cells are highlighted yellow in a specific row, if that makes more sense. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Cells with Conditional Formatting
Jason:
Here's your topic - goto: http://www.cpearson.com/excel/CFColors.htm Good luck!! and Happy 2006!! Jim "JasonC" wrote: Jim, I can't seem to get it to work. I have the file with me. Could I send it to you and you set it up for me? JasonC Thanks, JasonC "Jim May" wrote: Here's a UDF (Put in a Standard module) and use it in cell A1, like =CountByColor(A8:H8,6) << where 6 (at present) = Yellow = change to =CountByColor(A8:H8,3) to get the reds HTH Function CountByColor(InRange As Range, WhatColorIndex As Integer) As Long ' Dim c As Range CountByColor = 0 Application.Volatile True For Each c In InRange.Cells If c.Interior.ColorIndex = WhatColorIndex Then TCount = TCount + 1 End If Next c CountByColor = TCount End Function "JasonC" wrote: Is there a way to look at a row of data that is either highlighted yellow or red and to count only the data that is highlighted yellow and give me that number? None of my data is numerical....just one letter data, i.e.: "S" or "U" I just want to count how many of the cells are highlighted yellow in a specific row, if that makes more sense. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Cells with Conditional Formatting
One way of turning conditional formatting into regular formatting
is to copy your data array to Word, and then bring it back to Excel. Another, harder way is to make a copy to another location in your workbook, write your conditional formulas as number generating formulas and Find (number), Replace (format) and Copy, Paste Special, Format to the original array. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Conditional formatting...cont. from 9/25 | Excel Discussion (Misc queries) | |||
Conditional Formatting Error | Excel Worksheet Functions | |||
Copying cells with conditional formatting | Excel Discussion (Misc queries) | |||
conditional formatting adjacent cells | Excel Worksheet Functions |