SumProduct by Color of Cell

Henry

Board Regular
Joined
Mar 7, 2002
Messages
180
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Iridium

Well-known Member
Joined
Jul 15, 2002
Messages
2,831
Henry said:
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)
Code:
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Henry said:
...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

New Member
Joined
Nov 28, 2008
Messages
1
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,096
Messages
5,768,058
Members
425,451
Latest member
JohnBrooksBiddle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top