Conditional Formatting - How to get the cell colorindex

[hstaubyn]

I have a range of numbers which is subject to conditional formatting. The conditional formatting applied is a three-color-scale going from 0 to the highest value (red to yellow to green).

I have a for loop that looks at each cell in the range in turn and then sets the interior colour of some corresponding shapes depending on the colour of the cell, meaning that I need to get the colorindex rather than just reapplying the same conditional formatting somewhere else. This is fine when the cells are just filled normally, you can use the range("X100").interior.colorindex property and apply it to the relevant shape. This does not work, however, with conditional formatting. I have searched this and other sites, but the solutions I have found do not work. This is the code I have at the moment simplified with the troublesome line included:

When I run it I get a run-time error '438', Object doesn't support this property or method. I'm using Excel 2007.

Sub SetAllTechBubbleColour()


Dim iRange As Range


For Each iRange In Sheets("Sheet 2").Range("TechRankBubbleColour")
    iRange.Interior.ColorIndex = ActiveSheet.Range("H189").FormatConditions(1).Interior.ColorIndex
    MsgBox "Ok?"
Next iRange


End Sub

Strangely, these snippets of code do work:

Msgbox(Range("H189").FormatConditions.Count)

and

Msgbox(Range("H189").FormatConditions(1).Type)

I have also tried these approaches without success (same error at same point in code, i.e. when accessing the range's format conditions).

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

http://www.xldynamic.com/source/xld.CFConditions.html

Conditional Formatting Colors

In these approaches the same error occurs at this point (or similar):

Public Function CFColorindex(rng As Range, _
                      Optional text As Boolean = False)
'---------------------------------------------------------------------
Dim oFC As FormatCondition
Dim sF1 As String
Dim iRow As Long
Dim iColumn As Long


   Set rng = rng(1, 1)
   If rng.FormatConditions.Count > 0 Then
      [B]For Each oFC In rng.FormatConditions[/B]

If anyone could tell me where I'm going wrong I would be extremely grateful.

 

[VoG]

This may give you some pointers Conditional Formatting Colors

 

[hstaubyn]

Thanks for that suggestion, I have already tried it though and had the same problem with that code.

I have come across an excellent little work around though:

1. Copy the cells with the conditional formatting and paste into MS Word
2. Copy the newly pasted in table from MS Word
3. Select a cell in the excel spreadsheet and paste

The newly pasted table contains the same data and colours, but no conditional formatting!

Thank you Ashish for the suggestion - https://groups.google.com/forum/?fromgroups#!topic/microsoft.public.excel.misc/3kbkrAHUjIs%5B1-25%5D