Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
David Britton via OfficeKB.com
 
Posts: n/a
Default Excel Macro Question about Conditional Formatting

I have a cell that is colored according to a conditional formatting
statement. I want to copy the color to another cell using a macro but I
can't find the property. In particular, Range.Interior.ColorIndex is NOT
set. I can examine the colors using Range.FormatConditions(index)
..Interior.ColorIndex but I can't tell what value to use for "index" (a
conditional format has up to 3 conditions and thus index is a number
between 1 and 3 but I don't know which one is currently active).

--
Message posted via http://www.officekb.com
  #2   Report Post  
 
Posts: n/a
Default

hi,
you are coping the conditional format to the other cell.
if the condition isn't there then the color wont show.
if you want the color in the other cell then best just set
the color.
range("a1").interior.colorindex = 42
if you don't know the color indexs then in a blank
workbook past the folowing in a module. save it for future
reference.
Sub macGetColors()
' Macro written 2/10/02 by FSt1
Sheets("Sheet1").Select
Range("B2").Select
Set ci = Range("A1")
ci.Value = 1
Set c = Range("B2")
Do Until ci 56
Set c2 = c.Offset(1, 0)
Set cnum = c.Offset(0, 1)
c.Interior.ColorIndex = ci.Value
c.Offset(0, 1) = ci.Value
ci.Value = ci.Value + 1
Set c = c2
c.Select
Loop

End Sub

-----Original Message-----
I have a cell that is colored according to a conditional

formatting
statement. I want to copy the color to another cell using

a macro but I
can't find the property. In particular,

Range.Interior.ColorIndex is NOT
set. I can examine the colors using Range.FormatConditions

(index)
..Interior.ColorIndex but I can't tell what value to use

for "index" (a
conditional format has up to 3 conditions and thus index

is a number
between 1 and 3 but I don't know which one is currently

active).

--
Message posted via http://www.officekb.com
.

  #3   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

Chip Pearson has code for working with conditional formatting colours:

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

David Britton via OfficeKB.com wrote:
I have a cell that is colored according to a conditional formatting
statement. I want to copy the color to another cell using a macro but I
can't find the property. In particular, Range.Interior.ColorIndex is NOT
set. I can examine the colors using Range.FormatConditions(index)
.Interior.ColorIndex but I can't tell what value to use for "index" (a
conditional format has up to 3 conditions and thus index is a number
between 1 and 3 but I don't know which one is currently active).



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

  #4   Report Post  
David Britton via OfficeKB.com
 
Posts: n/a
Default

Thanks for the pointers. Bottom line is that you can't do it. Chip
Pearson's marco's parse the formulae and test which condition is set but,
as noted, this won't work unless the formula have absolute references. I
have many hundred different cells who's colour is set according to a
conditional format statement which include relative references to
neighbouring cells, and it is impractical to convert these to absolute
references. Seems very odd to me that you can't access this information.

--
Message posted via http://www.officekb.com
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 2 May 13th 23 11:46 AM
More than 3 Conditional Formatting Conditions Beth H Excel Worksheet Functions 12 January 6th 06 08:35 PM
An easy macro question and one I believe to be a little more diffi TroutKing Excel Worksheet Functions 3 January 18th 05 10:17 PM
Adding more than three Conditions to 'Conditional Formatting' David McRitchie Excel Discussion (Misc queries) 1 November 27th 04 07:03 PM
conditional formatting Graham Warren Excel Worksheet Functions 2 November 7th 04 06:04 PM


All times are GMT +1. The time now is 11:52 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"