# Identify Cell Color using a Function

#### Locky

##### New Member
I have a 10,000 line spreadsheet containing 194 red (bold)coloured cells in column 1. Is there any function that will return a different value so that I can sort the coloured cells differently and thus delete the other 9000 odd cells.
(I am a newbie that was the Guru on Lotus123 in 1985 and now find myself in a time warp)

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
On 2002-08-29 02:21, Locky wrote:
I have a 10,000 line spreadsheet containing 194 red (bold)coloured cells in column 1. Is there any function that will return a different value so that I can sort the coloured cells differently and thus delete the other 9000 odd cells.
(I am a newbie that was the Guru on Lotus123 in 1985 and now find myself in a time warp)

Two options:

a) Use the condition under which some cells have been red colored as criteria in Advanced Filter in order to extract the intended records/cells.

b) Use the following in additional column:

=ExtCell("backgroundcolor",a2)=3

and sort the data on the additional column in descending order to get the read colored cells on top.

Do a Search on this site with Advanced Filter as keyword or ExtCell as keyword. ExtCell is user-defined function, the code of which is posted on this site in one of the threads you can find with Search.

Hi Locky,

Welcome to the board.

Try Dave Hawley's site here:
http://www.microsoftexceltraining.com/

Go to VBA/Custom Functions, the Sum by colour and Count by colour functions should get you started in the right direction.

HTH

On 2002-08-29 03:32, Richie(UK) wrote:
Hi Locky,

Welcome to the board.

Try Dave Hawley's site here:
http://www.microsoftexceltraining.com/

Go to VBA/Custom Functions, the Sum by colour and Count by colour functions should get you started in the right direction.

HTH
Thanks Ritchie...used the Color Ranking (with a Tweak) and it worked great..

Replies
11
Views
807
Replies
4
Views
451
Replies
2
Views
507
Replies
4
Views
245
Replies
3
Views
711

1,220,965
Messages
6,157,123
Members
451,399
Latest member
alchavar

### 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.

### Which adblocker are you using?

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

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