Identifying the Color Index in a cell

  • Thread starter Thread starter BillRobPV
  • Start date Start date
B

BillRobPV

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
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
Macropod; Code runs fine (with ColorIndex). Now I'll test with conditionally
formatted cells. Thanks much.
--
Bill Roberts


BillRobPV said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
 
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.
 
Back
Top