|  | Identifying the Color Index in a cell | | |
| | | BillRobPV |  |
|  Posted: Tue Sep 02, 2008 8:54 pm Post subject: Identifying the Color Index in a cell |  |
This is a new post, but relevant to my old post "Not Copying Conditional
Formats".
I color (fill) a series of cells (not with any conditional format). Then
run a simple macro with the line
CI = activecell(rowno,"A").Interior.ColorIndex
It always returns CI=-4142 for every row
Then I have a command line
selection.interior.colorindex=CI which clears the color.
The intent is to (someday) replace a conditional format with a specified
color that does not change when I sort, etc.
On a different subject, I have worked all weekend with pearson.com. I made
his functions into an xla file, did the "addins" as well as I knew how, and
made them all public functions, but I cannot access them from an macro.
Ideas would be appreciated, but I'd like to figure out why the really simple
macro described above does not work (before I tackle Pearson again).. TIA
Bill Roberts
--
Bill Roberts |
| |
| | | macropod | |
| | Posted: Wed Sep 03, 2008 11:04 am Post subject: Re: Identifying the Color Index in a cell | |
| |  | |
Hi Bill,
A ColorIndex of -4142 equates to xlColorIndexNone. In other words, the colour isn't one of the 56 for which a ColorIndex exists.
For your purposes, you may get better results with something based on:
Sub Test()
Dim RowNo As Long
Dim ColVal As Long
RowNo = 10
ColVal = ActiveSheet.Cells(RowNo, "A").Interior.Color
MsgBox ColVal
End Sub
--
Cheers
macropod
[MVP - Microsoft Word]
"BillRobPV" <BillRobPV@discussions.microsoft.com> wrote in message news:866B7AEE-9816-4522-879D-45C0FAFAC11D@microsoft.com...
| Quote: | This is a new post, but relevant to my old post "Not Copying Conditional
Formats".
I color (fill) a series of cells (not with any conditional format). Then
run a simple macro with the line
CI = activecell(rowno,"A").Interior.ColorIndex
It always returns CI=-4142 for every row
Then I have a command line
selection.interior.colorindex=CI which clears the color.
The intent is to (someday) replace a conditional format with a specified
color that does not change when I sort, etc.
On a different subject, I have worked all weekend with pearson.com. I made
his functions into an xla file, did the "addins" as well as I knew how, and
made them all public functions, but I cannot access them from an macro.
Ideas would be appreciated, but I'd like to figure out why the really simple
macro described above does not work (before I tackle Pearson again).. TIA
Bill Roberts
--
Bill Roberts |
|
| |
| | | BillRobPV | |
| | Posted: Wed Sep 03, 2008 2:20 pm Post subject: Re: Identifying the Color Index in a cell | |
| | | |
Macropod; Thanks. I will test the code today. Part of my question was that
I know that the cell is filled with an acceptable (one of 56) color, and I
can't understand why the line I wrote can't identify it. I'll reply again
later when I test your suggestion.
--
Bill Roberts
"macropod" wrote:
| Quote: | Hi Bill,
A ColorIndex of -4142 equates to xlColorIndexNone. In other words, the colour isn't one of the 56 for which a ColorIndex exists.
For your purposes, you may get better results with something based on:
Sub Test()
Dim RowNo As Long
Dim ColVal As Long
RowNo = 10
ColVal = ActiveSheet.Cells(RowNo, "A").Interior.Color
MsgBox ColVal
End Sub
--
Cheers
macropod
[MVP - Microsoft Word]
"BillRobPV" <BillRobPV@discussions.microsoft.com> wrote in message news:866B7AEE-9816-4522-879D-45C0FAFAC11D@microsoft.com...
This is a new post, but relevant to my old post "Not Copying Conditional
Formats".
I color (fill) a series of cells (not with any conditional format). Then
run a simple macro with the line
CI = activecell(rowno,"A").Interior.ColorIndex
It always returns CI=-4142 for every row
Then I have a command line
selection.interior.colorindex=CI which clears the color.
The intent is to (someday) replace a conditional format with a specified
color that does not change when I sort, etc.
On a different subject, I have worked all weekend with pearson.com. I made
his functions into an xla file, did the "addins" as well as I knew how, and
made them all public functions, but I cannot access them from an macro.
Ideas would be appreciated, but I'd like to figure out why the really simple
macro described above does not work (before I tackle Pearson again).. TIA
Bill Roberts
--
Bill Roberts
|
|
| |
| | | BillRobPV | |
| | Posted: Wed Sep 03, 2008 3:04 pm Post subject: Re: Identifying the Color Index in a cell | |
| | | |
Macropod; Code runs fine (with ColorIndex). Now I'll test with conditionally
formatted cells. Thanks much.
--
Bill Roberts
"BillRobPV" wrote:
| Quote: | Macropod; Thanks. I will test the code today. Part of my question was that
I know that the cell is filled with an acceptable (one of 56) color, and I
can't understand why the line I wrote can't identify it. I'll reply again
later when I test your suggestion.
--
Bill Roberts
"macropod" wrote:
Hi Bill,
A ColorIndex of -4142 equates to xlColorIndexNone. In other words, the colour isn't one of the 56 for which a ColorIndex exists.
For your purposes, you may get better results with something based on:
Sub Test()
Dim RowNo As Long
Dim ColVal As Long
RowNo = 10
ColVal = ActiveSheet.Cells(RowNo, "A").Interior.Color
MsgBox ColVal
End Sub
--
Cheers
macropod
[MVP - Microsoft Word]
"BillRobPV" <BillRobPV@discussions.microsoft.com> wrote in message news:866B7AEE-9816-4522-879D-45C0FAFAC11D@microsoft.com...
This is a new post, but relevant to my old post "Not Copying Conditional
Formats".
I color (fill) a series of cells (not with any conditional format). Then
run a simple macro with the line
CI = activecell(rowno,"A").Interior.ColorIndex
It always returns CI=-4142 for every row
Then I have a command line
selection.interior.colorindex=CI which clears the color.
The intent is to (someday) replace a conditional format with a specified
color that does not change when I sort, etc.
On a different subject, I have worked all weekend with pearson.com. I made
his functions into an xla file, did the "addins" as well as I knew how, and
made them all public functions, but I cannot access them from an macro.
Ideas would be appreciated, but I'd like to figure out why the really simple
macro described above does not work (before I tackle Pearson again).. TIA
Bill Roberts
--
Bill Roberts
|
|
| |
| | | Gord Dibben | |
| | Posted: Wed Sep 03, 2008 4:42 pm Post subject: Re: Identifying the Color Index in a cell | |
Won't work with CF colored cells.
See Chip Pearson's site for more info and code.
LINK
Gord Dibben MS Excel MVP
On Wed, 3 Sep 2008 10:04:01 -0700, BillRobPV
<BillRobPV@discussions.microsoft.com> wrote:
| Quote: | Macropod; Code runs fine (with ColorIndex). Now I'll test with conditionally
formatted cells. Thanks much. |
|
| |
| | | BillRobPV | |
| | Posted: Wed Sep 03, 2008 6:26 pm Post subject: Re: Identifying the Color Index in a cell | |
You are So right! I am just at the point where I can step through Pearson's
code and get the ColorIndex, but (last minute news), the code doesn't seem to
eliminate the conditional formatting. Any instructions would be welcome.
TIA
--
Bill Roberts
"Gord Dibben" wrote:
| Quote: | Won't work with CF colored cells.
See Chip Pearson's site for more info and code.
LINK
Gord Dibben MS Excel MVP
On Wed, 3 Sep 2008 10:04:01 -0700, BillRobPV
BillRobPV@discussions.microsoft.com> wrote:
Macropod; Code runs fine (with ColorIndex). Now I'll test with conditionally
formatted cells. Thanks much.
|
|
| |
| | | BillRobPV | |
| | Posted: Wed Sep 03, 2008 6:42 pm Post subject: Re: Identifying the Color Index in a cell | |
I got it!!. Just takes selection.formatconditions.delete Looks good, but it
is not easy. I need to study Pearson's code. Thanks for your help.
--
Bill Roberts
"BillRobPV" wrote:
| Quote: | You are So right! I am just at the point where I can step through Pearson's
code and get the ColorIndex, but (last minute news), the code doesn't seem to
eliminate the conditional formatting. Any instructions would be welcome.
TIA
--
Bill Roberts
"Gord Dibben" wrote:
Won't work with CF colored cells.
See Chip Pearson's site for more info and code.
LINK
Gord Dibben MS Excel MVP
On Wed, 3 Sep 2008 10:04:01 -0700, BillRobPV
BillRobPV@discussions.microsoft.com> wrote:
Macropod; Code runs fine (with ColorIndex). Now I'll test with conditionally
formatted cells. Thanks much.
|
|
| |
|
|
