Get Displayed Cell Color (whether from Conditional Formatting or not)

**gembree** · 10-11-2013, 09:53 PM

Originally Posted by Rick Rothstein

VB does does not provide direct access to the color displayed in a cell.

**deanmosh** · 10-11-2013, 10:59 PM

Gembree--

Thanks so much for replying! I figured I would never get an answer. I've been copy and pasting my formatting to word and copying them back in, but I really hope i can get the Function from your link to work.

Hoping you could answer a couple of quick questions for since this is on your radar..

1. For Rick's function, and other functions in general, i can't: When someone deems a function as NOT a UDF, then what is it? What i mean is, if it's not a UDF then how is the function called?

2. For the function, and other functions in general, is this the correct process to get it the work correctly? (Sorry, I've really tried to find a straight forward answer to this but haven't been able to get a clear response):

1. Copy/paste the code below into a "New Module" via VBA editor

Code:

Function getColorIndex()   
getColorIndex = ActiveCell.Interior.ColorIndex
End Function

2. Return to relevant workbook/spreadsheet, and for all cells with conditional formatting, type: "=getColorIndex()"? OR do I need to fill in the values with the ()? If so, what would those values be?

If that's the correct way to do this then can I just copy and that cell to all relevant cells and it should spit out the actual fill color, which I can then copy and paste to other excel docs?

Sorry for the really annoyingly basic questions. I'm by no means an expert at any of this.. just trying to make better use of my time.

Thanks in advance for any help!!!!

**gembree** · 10-11-2013, 11:49 PM

Originally Posted by deanmosh

1. For Rick's function, and other functions in general, i can't: When someone deems a function as NOT a UDF, then what is it? What i mean is, if it's not a UDF then how is the function called?

These are "Macros", called from View Macros (Alt + F8). Rather than returning a value to a cell like a normal Excel function, they typically directly edit the spreadsheet.

2. For the function, and other functions in general, is this the correct process to get it the work correctly?

Close, but two things:

1) As noted, reading conditional formatting doesn't mesh well with UDFs. For reading conditional formatting specifically, you really need to run a basic macro, like this one:

Code:

Sub GetTheColors()
For i = 1 to 100
     For j = 1 to 10
          Cells(i,j+10) = Cells(i,j).FormatConditions.Interior.ColorIndex
     Next j
Next i
End Sub

This writes the color indices of A1:J100 into K1:T100 when you run it.

2) Using ActiveCell specifically in UDFs isn't a good idea, because ActiveCell refers to where the cursor is, not where the formula is. You want to pass the range in question as an argument to the function:

Code:

Function getColorIndex(TheColoredCell)   
getColorIndex = TheColoredCell.Interior.ColorIndex
End Function

And then call the function as =getColorIndex(C37)

If that's the correct way to do this then can I just copy and that cell to all relevant cells and it should spit out the actual fill color, which I can then copy and paste to other excel docs?

Yes. You may need to use a "hard" recalculate [Ctrl+Alt+F9] to get it to update with any changes you've made to the color for arcane Excel reasons, though.

**deanmosh** · 10-17-2013, 04:03 AM

Hi Again Gembree--

and again thanks for the detailed help. I feel like im almost there..
Followed your directions to try and return the actual colors of my conditional formatting, but came back with some errors.. and i was hoping you might be able to lend some advice again..

I'll try and outline the issues as clearly as possible for you:

(1.) As a complete novice, i feel that my mistake must be how i entered the Sub you provided

Code:

Sub GetTheColors()
For i = 1 to 100
     For j = 1 to 10
          Cells(i,j+10) = Cells(i,j).FormatConditions.Interior.ColorIndex
     Next j
Next i
End Sub

You indicated that this will return the real colors for cells A-J100. And in an attempt to tailor the code for my particular workbook i came up with this (i included a screencap of what I working off of) :

Code:

Sub GetTheColors()
For ad = 1 To 1200
     For ab = 1 To 10
          Cells(ad, ab + 10) = Cells(ad, ab).FormatConditions.Interior.ColorIndex
     Next ab
Next ad
End Sub

I used "AB" because even though the cells aren't formatted, I would prefer that actual colors be written after column AB (the image below only shows where the conditional formatting was applied)

Capture.jpg

When I try to run the Sub above from the macros within excel, it comes back with this error:

Capture2.jpg

With this line highlighted after debugging: " Cells(ad, ab + 10) = Cells(ad, ab).FormatConditions.Interior.ColorIndex"

*Issue #1 might be solved after you take a look at my 2nd question, but i can't be sure.

(2.) Is the Fucntion code you provided (below) intended to be copy pasted within the same module as SubGetTheColors?:

Code:

Function getColorIndex(TheColoredCell)   
getColorIndex = TheColoredCell.Interior.ColorIndex
End Function

If i assumed correctly in (2.), then should the resulting module contain both of the aforementioned codes? Also, just wondering if the code above to 'pass the range in question as an argument to the function,' should "(TheColoredCell)" actually be "(GetTheColors)"?

I'm just assuming that the Function should be calling the Sub...

AGAiN thanks so much for any help you can give.

**gembree** · 10-21-2013, 07:11 PM

Nope - that one is my own dumb mistake, Dean. I apologize. The right property is DisplayFormat, not FormatConditions.

In order to change the parameters, you want to change the numbers, not the letters.

1 to 1200 is the rows to check.
1 to 10 is the columns to check (i.e. A to J).
Cells(i+0,j+10) is where to write the output. This writes each ColorIndex 0 rows and 10 columns to the right.

Code:

Sub GetTheColors()

For i = 1 To 1200
     For j = 1 To 10
          Cells(i, j + 10) = Cells(i, j).DisplayFormat.Interior.ColorIndex
     Next j
Next i

End Sub

If you want to write A1:AA1200 to AB1:BB1200, use

Code:

For j = 1 To 27
     Cells(i, j + 27) = Cells(i, j).DisplayFormat.Interior.ColorIndex
Next j

Originally Posted by deanmosh

(2.) Is the Fucntion code you provided (below) intended to be copy pasted within the same module as SubGetTheColors?:

Code:

Function getColorIndex(TheColoredCell)   
getColorIndex = TheColoredCell.Interior.ColorIndex
End Function

Thread: Get Displayed Cell Color (whether from Conditional Formatting or not)

Thread Tools

Search Thread

Display

Similar Threads

Conditional formatting

Conditional Formatting using formula.

Conditional Formatting - If/And Formula

CONDITIONAL FORMATTING (WEEKENDS) - HOW TO CONTINUE ON THE NEXT COLUMNS

Color Cell Fill With Conditional Format When Value Less Than Or Greater Than X

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions