Any Excel gurus out there? HELP PLEASE

[Wapping]

Whilst I know how to sort and manage data within multiple cells, I wonder if any bright spark out there can help me, please?

I have a large spread sheet on my desk, which shows the result of lots of insurance survey reports, made of a client's many factories.

In essence, individual features (sprinkler protection, fire appliances, business recovery plan, housekeeping etc) for each of the 150 factories are colour coded, BLUE for excellent, GREEN for better than average, YELLOW for average, AMBER for worse than average, RED for very poor. There is no data in any of the cells, just the in-fill colour. This means the spreadsheet ends up like a large, multi-coloured chess board.

This is good as it gives the client's senior management (who know B all about insurance) a very good snap shot of the overall quality of 150 factories. Unless they are colour blind, they can quickly spot on the red coloured cells or whatever and can see what the problem is.

However, I want to quickly work out how many cells are coloured red, green, blue, amber, yellow.

For instance, if I know the total number of cells is 1,500 and that 10 are red (very bad) that is 0.66%, which means that 99.34% are better than very bad. I don't want to count individual cells, though as that is very tedious.

Is there anyway to sort or count cells that have no data, but are simply in-filled with a colour? If so, how?

Many thanks,

Richard

 

[jan]

vba:

Sub checkcolor()
For a = 1 To 5
If Range("a" & a).Interior.Color = vbRed Then
Range("b1") = "ok"
End If
Next a
End Sub

change range and colours to what you need

 

[Wapping]

vba:

Sub checkcolor()
For a = 1 To 5
If Range("a" & a).Interior.Color = vbRed Then
Range("b1") = "ok"
End If
Next a
End Sub

change range and colours to what you need

Wow, that was quick.

Excellent. I will try it (or rather my colleague will) in the morning.

Many thanks.

Richard

 

[Clive]

If you have a grid then you'll need a double loop
and you can check the ColorIndex (Red is 3) - something like

for x = 1 to howevermanyrows

for y = 1 to howevermanycolumns

if WorkSheets(1).Cells(x, y).Interior.ColorIndex = 3 then
countOfRed = countOfRed + 1
end if

next y

next x

You probably ought to extend this to check and count all 5 colours - then if the user has entered something that looks a bit red (or blue/amber etc) but isn't actually the value for "red" you can pick this up and investigate further

 

[Wapping]

This is incredible. Many thanks

I just hope Linda can understand it all

 

[brassmonkey001]

Try this.

You will need to put a key into the worksheet somewhere.
So for example, for your key you colour your cells A1 = Red, A2 = blue, A3 = yellow, then where you want your results (in this example B1) you insert function =ColorFunction(A1,C1:C60). This will count how many red cells in the range C1 to C60. Then in B2 insert the function =ColorFunction(A2,C1:C60), which will count how many blue cells in the same range. And so forth.

 

[Red Rock]

Looks like you've sorted this one, but I was faffing around at work yesterday and found this guy's channel on YouTube, which has got quite a few (>400) good hints and tips..especially for a quick overview of that function you could never be bothered to read up on!

http://uk.youtube.com/ExcelIsFun

 

[Garryc]

....cant get my head round vba - need to try and learn it.

BrassM - like ur option.