?
Solved

Displaying the color index of a cell

Posted on 2010-01-12
7
Medium Priority
?
251 Views
Last Modified: 2012-05-08
Want i'm trying to accomplish is:
In column A I have multiple cells with different colors in them in column B I wanted the color index of each colored cell, for example.
If column A cell A1 is red I wanted in column B, B1 to display 3 and so on.
0
Comment
Question by:SOUTHAMERICA70
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 26298883
Hello SOUTHAMERICA70,

Chip Pearson has numerous code examples showing
how to evaluate cell colors.

If the coloring is due to "normal" formatting:

http://www.cpearson.com/excel/colors.htm

If the coloring is from Conditional Formatting:

http://www.cpearson.com/excel/CFColors.htm


Regards,

Patrick
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 2000 total points
ID: 26298921
You can use XLM and range name workarounds for this (ie no VBA)
  1. Select Cell B1
  2. Insert a range name
     
         CellColour
=GET.CELL(63,A1)
       3. in B1 put  =CellColour+RAND()*0
       4. copy down

see attached file

     


cc.xls
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 26298935
Dave,

Brilliant!

Patrick
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Expert Comment

by:Dave Brett
ID: 26298952
thx Patrick, I had missed your earlier post

although new Excel security updates now flag XLM use, so the invisible macro is getting hard to use :)

Dave
0
 

Author Comment

by:SOUTHAMERICA70
ID: 26299125
Quick question...how do I use XLM?
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 26299145
It is used automatically by the range name, ie the
=GET.CELL(63,A1
portion is using a XLM formula to retrieve the cell backgrund colour (63 is the colour code)

Cheers
Dave
0
 

Author Comment

by:SOUTHAMERICA70
ID: 26299180
Got it.  Thanks.  Also I created a new excel sheet but when I place the  =CellColour+RAND()*0 in the cell I get a #NAME? error.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question