VBA Macro for Counting with while and nested if

[tharrington]

I am trying to create a macro that does the following.
while each one of 10 columns doesn't contain a red colored cell
count the # of yellow cells (highlighted through conditional formatting)
find row with max # of yellow cells
turn them red
display name of rows that turned red
end

the cells contain numerical values and are highlighted if they are within 5% of the max in that column

Any help would be greatly appreciated.
Any questions let me know
thanks

 

[ParamRay]

Here are some worksheet functions that might come in handy.

Suppose you want to count the cells in A1:C3 that are the same colour as cell E5.

Then you can use the syntax:
= COUNT_COLORED_CELLS(A1:C3, CELL_COLOR(E5))

Or, if you want to count the yellow cells in A1:C3, you could use:
= COUNT_COLORED_CELLS(A1:C3, {255, 255, 0})

Function COUNT_COLORED_CELLS(reference As Range, Color()) As Long
  Dim rgb_color_value As Long
  Dim cell As Range
  rgb_color_value = RGB(Color(1), Color(2), Color(3))
  For Each cell In reference.Cells
    If cell.Interior.Color = rgb_color_value Then
      COUNT_COLORED_CELLS = COUNT_COLORED_CELLS + 1
    End If
  Next cell
End Function

Function CELL_COLOR(reference As Range) As Byte()
  Dim color_components(1 To 3) As Byte
  Dim rgb_color_value As Long
  rgb_color_value = reference.Interior.Color
  color_components(1) = (rgb_color_value \ (256 ^ 0)) Mod 256
  color_components(2) = (rgb_color_value \ (256 ^ 1)) Mod 256
  color_components(3) = (rgb_color_value \ (256 ^ 2)) Mod 256
  CELL_COLOR = color_components
End Function

 

[tharrington]

Thanks for your response.

What is the syntax if I want to check each row in a column before moving to the next column?

reference.column?

Thanks again

 

[tharrington]

Bumping for help.