software

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.: quot;Squot; or quot;Uquot;

I just want to count how many of the cells are highlighted yellow in a
specific row, if that makes more sense.

Here's a UDF (Put in a Standard module)

and use it in cell A1, like =CountByColor(A8:H8,6) lt;lt; where 6 (at
present) = Yellow = change to =CountByColor(A8:H8,3) to get the reds
HTHFunction 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

quot;JasonCquot; wrote:

gt; Is there a way to look at a row of data that is either highlighted yellow or
gt; red and to count only the data that is highlighted yellow and give me that
gt; number?
gt;
gt; None of my data is numerical....just one letter data, i.e.: quot;Squot; or quot;Uquot;
gt;
gt; I just want to count how many of the cells are highlighted yellow in a
gt; specific row, if that makes more sense.

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?

JasonCThanks,

JasonC

quot;Jim Mayquot; wrote:

gt; Here's a UDF (Put in a Standard module)
gt;
gt; and use it in cell A1, like =CountByColor(A8:H8,6) lt;lt; where 6 (at
gt; present) = Yellow = change to =CountByColor(A8:H8,3) to get the reds
gt; HTH
gt;
gt;
gt; Function CountByColor(InRange As Range, WhatColorIndex As Integer) As Long
gt; '
gt; Dim c As Range
gt; CountByColor = 0
gt; Application.Volatile True
gt; For Each c In InRange.Cells
gt; If c.Interior.ColorIndex = WhatColorIndex Then
gt; TCount = TCount 1
gt; End If
gt; Next c
gt; CountByColor = TCount
gt; End Function
gt;
gt; quot;JasonCquot; wrote:
gt;
gt; gt; Is there a way to look at a row of data that is either highlighted yellow or
gt; gt; red and to count only the data that is highlighted yellow and give me that
gt; gt; number?
gt; gt;
gt; gt; None of my data is numerical....just one letter data, i.e.: quot;Squot; or quot;Uquot;
gt; gt;
gt; gt; I just want to count how many of the cells are highlighted yellow in a
gt; gt; specific row, if that makes more sense.

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

quot;JasonCquot; wrote:

gt; Jim,
gt;
gt; I can't seem to get it to work. I have the file with me. Could I send it to
gt; you and you set it up for me?
gt;
gt; JasonC
gt;
gt;
gt; Thanks,
gt;
gt; JasonC
gt;
gt; quot;Jim Mayquot; wrote:
gt;
gt; gt; Here's a UDF (Put in a Standard module)
gt; gt;
gt; gt; and use it in cell A1, like =CountByColor(A8:H8,6) lt;lt; where 6 (at
gt; gt; present) = Yellow = change to =CountByColor(A8:H8,3) to get the reds
gt; gt; HTH
gt; gt;
gt; gt;
gt; gt; Function CountByColor(InRange As Range, WhatColorIndex As Integer) As Long
gt; gt; '
gt; gt; Dim c As Range
gt; gt; CountByColor = 0
gt; gt; Application.Volatile True
gt; gt; For Each c In InRange.Cells
gt; gt; If c.Interior.ColorIndex = WhatColorIndex Then
gt; gt; TCount = TCount 1
gt; gt; End If
gt; gt; Next c
gt; gt; CountByColor = TCount
gt; gt; End Function
gt; gt;
gt; gt; quot;JasonCquot; wrote:
gt; gt;
gt; gt; gt; Is there a way to look at a row of data that is either highlighted yellow or
gt; gt; gt; red and to count only the data that is highlighted yellow and give me that
gt; gt; gt; number?
gt; gt; gt;
gt; gt; gt; None of my data is numerical....just one letter data, i.e.: quot;Squot; or quot;Uquot;
gt; gt; gt;
gt; gt; gt; I just want to count how many of the cells are highlighted yellow in a
gt; gt; gt; specific row, if that makes more sense.

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
quot;Jim Mayquot; wrote:

gt; Jason:
gt;
gt; I just noticed that my solution is not based on the use of conditional
gt; formatting,
gt; but rather based on your manually painting the cells. I now realize that
gt; you are
gt; using CF to get the colors red or yellow. Can you provide me a sample of your
gt; conditional formatting settings? Meanwhile I'll see if I can correct and
gt; resubmit
gt; what you want.
gt; Jim
gt;
gt; quot;JasonCquot; wrote:
gt;
gt; gt; Jim,
gt; gt;
gt; gt; I can't seem to get it to work. I have the file with me. Could I send it to
gt; gt; you and you set it up for me?
gt; gt;
gt; gt; JasonC
gt; gt;
gt; gt;
gt; gt; Thanks,
gt; gt;
gt; gt; JasonC
gt; gt;
gt; gt; quot;Jim Mayquot; wrote:
gt; gt;
gt; gt; gt; Here's a UDF (Put in a Standard module)
gt; gt; gt;
gt; gt; gt; and use it in cell A1, like =CountByColor(A8:H8,6) lt;lt; where 6 (at
gt; gt; gt; present) = Yellow = change to =CountByColor(A8:H8,3) to get the reds
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Function CountByColor(InRange As Range, WhatColorIndex As Integer) As Long
gt; gt; gt; '
gt; gt; gt; Dim c As Range
gt; gt; gt; CountByColor = 0
gt; gt; gt; Application.Volatile True
gt; gt; gt; For Each c In InRange.Cells
gt; gt; gt; If c.Interior.ColorIndex = WhatColorIndex Then
gt; gt; gt; TCount = TCount 1
gt; gt; gt; End If
gt; gt; gt; Next c
gt; gt; gt; CountByColor = TCount
gt; gt; gt; End Function
gt; gt; gt;
gt; gt; gt; quot;JasonCquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Is there a way to look at a row of data that is either highlighted yellow or
gt; gt; gt; gt; red and to count only the data that is highlighted yellow and give me that
gt; gt; gt; gt; number?
gt; gt; gt; gt;
gt; gt; gt; gt; None of my data is numerical....just one letter data, i.e.: quot;Squot; or quot;Uquot;
gt; gt; gt; gt;
gt; gt; gt; gt; I just want to count how many of the cells are highlighted yellow in a
gt; gt; gt; gt; specific row, if that makes more sense.

Jason:
Here's your topic - goto:
www.cpearson.com/excel/CFColors.htm
Good luck!!
and Happy 2006!!
Jim

quot;JasonCquot; wrote:

gt; Jim,
gt;
gt; I can't seem to get it to work. I have the file with me. Could I send it to
gt; you and you set it up for me?
gt;
gt; JasonC
gt;
gt;
gt; Thanks,
gt;
gt; JasonC
gt;
gt; quot;Jim Mayquot; wrote:
gt;
gt; gt; Here's a UDF (Put in a Standard module)
gt; gt;
gt; gt; and use it in cell A1, like =CountByColor(A8:H8,6) lt;lt; where 6 (at
gt; gt; present) = Yellow = change to =CountByColor(A8:H8,3) to get the reds
gt; gt; HTH
gt; gt;
gt; gt;
gt; gt; Function CountByColor(InRange As Range, WhatColorIndex As Integer) As Long
gt; gt; '
gt; gt; Dim c As Range
gt; gt; CountByColor = 0
gt; gt; Application.Volatile True
gt; gt; For Each c In InRange.Cells
gt; gt; If c.Interior.ColorIndex = WhatColorIndex Then
gt; gt; TCount = TCount 1
gt; gt; End If
gt; gt; Next c
gt; gt; CountByColor = TCount
gt; gt; End Function
gt; gt;
gt; gt; quot;JasonCquot; wrote:
gt; gt;
gt; gt; gt; Is there a way to look at a row of data that is either highlighted yellow or
gt; gt; gt; red and to count only the data that is highlighted yellow and give me that
gt; gt; gt; number?
gt; gt; gt;
gt; gt; gt; None of my data is numerical....just one letter data, i.e.: quot;Squot; or quot;Uquot;
gt; gt; gt;
gt; gt; gt; I just want to count how many of the cells are highlighted yellow in a
gt; gt; gt; specific row, if that makes more sense.

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.