SumProduct by Color of Cell

[Henry]

Hi:


I have a range of cells with different background colors thanks to Conditional Formatting. Can I use a formua that will count only the Reds, then Greens, etc.

Also, how can I get around "three only" conditional formatting? How can I use, lets' say, 5 colors?

Thanks Again

MT

 

[Iridium]

Hi:


I have a range of cells with different background colors thanks to Conditional Formatting. Can I use a formua that will count only the Reds, then Greens, etc.

Also, how can I get around "three only" conditional formatting? How can I use, lets' say, 5 colors?

Thanks Again

MT

For more than 3 conditions you'll need VBA e.g.

(Open the VBE (press Alt+F11) and paste this into the worksheet module)

Private Sub Worksheet_Change(ByVal Target As Range) 
'Getting past Conditional Formattings 3 Criteria Limit 
'This code must be placed in the Private Module of the Worksheet. _ 
To get there right click on the sheet name tab and select "View Code". 
'Excel's very handy Conditional Formatting unfortunately only allows up to 3 conditions. _ 
The method below gets around this limit. It is set to work on A1:A10 only. event. 

Dim icolor As Integer 
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then 
Select Case Target 
  Case 1 To 5 
     icolor = 6 
  Case 6 To 10 
     icolor = 12 
  Case 11 To 15 
     icolor = 7 
  Case 16 To 20 
     icolor = 53 
  Case 21 To 25 
     icolor = 15 
  Case 26 To 30 
     icolor = 42 
  Case Else 
   'Whatever you want 
End Select 
  Target.Interior.ColorIndex = icolor 
End If 
    
End Sub

Also there's some stuff at Chip's site for working with conditional foormats at http://www.cpearson.com/excel/CFColors.htm

HTH

 

[Aladin Akyurek]

...I have a range of cells with different background colors thanks to Conditional Formatting. Can I use a formua that will count only the Reds, then Greens, etc...

You can transform the formulas of the conditional formatting into formulas that count.

 

[Zeuzism]

OK, thanks. I tried that. But what happens next? I'm sorry I am quite new to VBA... If I want to run the macro is not there. How can I actually run it?

Thanks for the trouble.