Hi anyone,

recently i put all worksheet formula into vba. Application now runs quite
fast except for checking each cell having conditional formula to make null,
zero-values and cells having the same value as the cell above invisible by
using backgroundcolor as fontcolor.
Changing the color within vba-code itself is not the problem. But I will
loose information about regular cell-color once I change FontColor. In the
next run it has to be changed back to regular color. I wonder, where
microsoft stores the "regular" coloring of a cell since the conditional
formula only knows the conditional color.

regards
willi

Activecell.Interior.ColorIndex
Activecell.font.colorIndex

never changes regardless of what conditional formatting is displaying.

--
Regards,
Tom Ogilvy


"Gulli" wrote:

Hi anyone,

recently i put all worksheet formula into vba. Application now runs quite
fast except for checking each cell having conditional formula to make null,
zero-values and cells having the same value as the cell above invisible by
using backgroundcolor as fontcolor.
Changing the color within vba-code itself is not the problem. But I will
loose information about regular cell-color once I change FontColor. In the
next run it has to be changed back to regular color. I wonder, where
microsoft stores the "regular" coloring of a cell since the conditional
formula only knows the conditional color.

regards
willi

Hi Tom,

thanks for quick response. Is your suggestion correct only for ActiveCell ?
The problem is, I loop through a matrix of cells starting from an activecell
somewhere else. So all the target cells whose value will conditionally change
won't become active. Depending on input in "Source"Cell the values in
TargetCells will often flip between 0, empty or same Value as cell above and
another value which has to be shown.
Since worksheet.cells(r, c).font.color and .colorindex reflect always the
currently given value and activecell still remains on start-Cell, I found no
location, where the regular fontcolor is stored. Could i use the Color-field
"worksheet.cells(r, c).FormatConditions.Item1.Interior.Color" and store the
normal color ?

regards
gulli


I

"Tom Ogilvy" wrote:

Activecell.Interior.ColorIndex
Activecell.font.colorIndex

never changes regardless of what conditional formatting is displaying.

--
Regards,
Tom Ogilvy


"Gulli" wrote:

Hi anyone,

recently i put all worksheet formula into vba. Application now runs quite
fast except for checking each cell having conditional formula to make null,
zero-values and cells having the same value as the cell above invisible by
using backgroundcolor as fontcolor.
Changing the color within vba-code itself is not the problem. But I will
loose information about regular cell-color once I change FontColor. In the
next run it has to be changed back to regular color. I wonder, where
microsoft stores the "regular" coloring of a cell since the conditional
formula only knows the conditional color.

regards
willi

Activecell was use as a representative range object.

Every single cell maintains its colorindex value for both
interior.colorindex and font.colorindex regardless of what color is being
shown by conditional formatting.

If you color a cell red manually, then apply a conditional format that makes
the cells (b9 for example) yellow,

msgbox Range("B9").Interior.ColorIndex

willl display 3 for red.

there is no reason to try to store the original color anywhere, nor is there
any provision for you to do so.

--
Regards,
Tom Ogilvy


"Gulli" wrote:

Hi Tom,

thanks for quick response. Is your suggestion correct only for ActiveCell ?
The problem is, I loop through a matrix of cells starting from an activecell
somewhere else. So all the target cells whose value will conditionally change
won't become active. Depending on input in "Source"Cell the values in
TargetCells will often flip between 0, empty or same Value as cell above and
another value which has to be shown.
Since worksheet.cells(r, c).font.color and .colorindex reflect always the
currently given value and activecell still remains on start-Cell, I found no
location, where the regular fontcolor is stored. Could i use the Color-field
"worksheet.cells(r, c).FormatConditions.Item1.Interior.Color" and store the
normal color ?

regards
gulli


I

"Tom Ogilvy" wrote:

Activecell.Interior.ColorIndex
Activecell.font.colorIndex

never changes regardless of what conditional formatting is displaying.

--
Regards,
Tom Ogilvy


"Gulli" wrote:

Hi anyone,

recently i put all worksheet formula into vba. Application now runs quite
fast except for checking each cell having conditional formula to make null,
zero-values and cells having the same value as the cell above invisible by
using backgroundcolor as fontcolor.
Changing the color within vba-code itself is not the problem. But I will
loose information about regular cell-color once I change FontColor. In the
next run it has to be changed back to regular color. I wonder, where
microsoft stores the "regular" coloring of a cell since the conditional
formula only knows the conditional color.

regards
willi

Hi Tom,
many thanks for response.

excuse me - maybe I don't understand something. The situation is the
following :
first I installed conditional formatting on worksheet level. It works, no
doubt. But as with other stuff like formulas it is a huge job, replacing
changed formulas if you have thousands of cells spread on a certain amount of
worksheets, which do nearly the same things. So I replaced the formulas by
vba-call. Now there is only one formula wich carries out computations on 10
target cells row by row. The cells dont know any formula at all. So far so
good. For performance matters I switch automatic calculations off as well as
screen updating while vba is calculating.
Still for performance reasons I tried to move conditional formatting from
worksheet to vba.
Now I find no property "conditionalFontColor" or something like that. So
coding changes "conditionally" a cell by following code snippet :

...
...
with worksheet.cells(r, z)
if .value = above_value or .value = 0 then
.fontcolor = noshow_value
end if
end with
...
...

but with the next loop ( I changed some value in source-Cell, so
target-Cells have been recomputed ), now the condition ( "if .value =
abbove.value ... ") is not met.
The noshow_color will remain as fontColor - the cell now has different Value
than the cell above and value is < 0, it is still shown with noshow_color,
which is actually the background color.
I added an else to Code like

if .value = above_value or .value = 0 then
.fontcolor = noshow_value
else
.fontcolor = ? 'should_be_normal_fontColor ( which may be different
columnwise )
end if

...
...

This situation is the reason, why I tried to get information, how MS-Excel
keeps the "normal" cell-FontColor between multiple cycles of reformatting
conditionally.

regards
Gulli


"Tom Ogilvy" wrote:

Activecell was use as a representative range object.

Every single cell maintains its colorindex value for both
interior.colorindex and font.colorindex regardless of what color is being
shown by conditional formatting.

If you color a cell red manually, then apply a conditional format that makes
the cells (b9 for example) yellow,

msgbox Range("B9").Interior.ColorIndex

willl display 3 for red.

there is no reason to try to store the original color anywhere, nor is there
any provision for you to do so.

--
Regards,
Tom Ogilvy


"Gulli" wrote:

Hi Tom,

thanks for quick response. Is your suggestion correct only for ActiveCell ?
The problem is, I loop through a matrix of cells starting from an activecell
somewhere else. So all the target cells whose value will conditionally change
won't become active. Depending on input in "Source"Cell the values in
TargetCells will often flip between 0, empty or same Value as cell above and
another value which has to be shown.
Since worksheet.cells(r, c).font.color and .colorindex reflect always the
currently given value and activecell still remains on start-Cell, I found no
location, where the regular fontcolor is stored. Could i use the Color-field
"worksheet.cells(r, c).FormatConditions.Item1.Interior.Color" and store the
normal color ?

regards
gulli


I

"Tom Ogilvy" wrote:

Activecell.Interior.ColorIndex
Activecell.font.colorIndex

never changes regardless of what conditional formatting is displaying.

--
Regards,
Tom Ogilvy


"Gulli" wrote:

Hi anyone,

recently i put all worksheet formula into vba. Application now runs quite
fast except for checking each cell having conditional formula to make null,
zero-values and cells having the same value as the cell above invisible by
using backgroundcolor as fontcolor.
Changing the color within vba-code itself is not the problem. But I will
loose information about regular cell-color once I change FontColor. In the
next run it has to be changed back to regular color. I wonder, where
microsoft stores the "regular" coloring of a cell since the conditional
formula only knows the conditional color.

regards
willi

No, excel does not see your code as conditionally formatting the cell. If
you extend your reasoning, then almost any action accomplished which code
that includes an IF statement would be considered condititional formatting.


If you want to restore the color, then you will have to record the original
color.

No provision is made for this.

Perhaps you just want to use

xlAutomatic for font.colorindex
or
xlNone for interior.colorindex

--
Regards,
Tom Ogilvy




"Gulli" wrote:

Hi Tom,
many thanks for response.

excuse me - maybe I don't understand something. The situation is the
following :
first I installed conditional formatting on worksheet level. It works, no
doubt. But as with other stuff like formulas it is a huge job, replacing
changed formulas if you have thousands of cells spread on a certain amount of
worksheets, which do nearly the same things. So I replaced the formulas by
vba-call. Now there is only one formula wich carries out computations on 10
target cells row by row. The cells dont know any formula at all. So far so
good. For performance matters I switch automatic calculations off as well as
screen updating while vba is calculating.
Still for performance reasons I tried to move conditional formatting from
worksheet to vba.
Now I find no property "conditionalFontColor" or something like that. So
coding changes "conditionally" a cell by following code snippet :

..
..
with worksheet.cells(r, z)
if .value = above_value or .value = 0 then
.fontcolor = noshow_value
end if
end with
..
..

but with the next loop ( I changed some value in source-Cell, so
target-Cells have been recomputed ), now the condition ( "if .value =
abbove.value ... ") is not met.
The noshow_color will remain as fontColor - the cell now has different Value
than the cell above and value is < 0, it is still shown with noshow_color,
which is actually the background color.
I added an else to Code like

if .value = above_value or .value = 0 then
.fontcolor = noshow_value
else
.fontcolor = ? 'should_be_normal_fontColor ( which may be different
columnwise )
end if

..
..

This situation is the reason, why I tried to get information, how MS-Excel
keeps the "normal" cell-FontColor between multiple cycles of reformatting
conditionally.

regards
Gulli


"Tom Ogilvy" wrote:

Activecell was use as a representative range object.

Every single cell maintains its colorindex value for both
interior.colorindex and font.colorindex regardless of what color is being
shown by conditional formatting.

If you color a cell red manually, then apply a conditional format that makes
the cells (b9 for example) yellow,

msgbox Range("B9").Interior.ColorIndex

willl display 3 for red.

there is no reason to try to store the original color anywhere, nor is there
any provision for you to do so.

--
Regards,
Tom Ogilvy


"Gulli" wrote:

Hi Tom,

thanks for quick response. Is your suggestion correct only for ActiveCell ?
The problem is, I loop through a matrix of cells starting from an activecell
somewhere else. So all the target cells whose value will conditionally change
won't become active. Depending on input in "Source"Cell the values in
TargetCells will often flip between 0, empty or same Value as cell above and
another value which has to be shown.
Since worksheet.cells(r, c).font.color and .colorindex reflect always the
currently given value and activecell still remains on start-Cell, I found no
location, where the regular fontcolor is stored. Could i use the Color-field
"worksheet.cells(r, c).FormatConditions.Item1.Interior.Color" and store the
normal color ?

regards
gulli


I

"Tom Ogilvy" wrote:

Activecell.Interior.ColorIndex
Activecell.font.colorIndex

never changes regardless of what conditional formatting is displaying.

--
Regards,
Tom Ogilvy


"Gulli" wrote:

Hi anyone,

recently i put all worksheet formula into vba. Application now runs quite
fast except for checking each cell having conditional formula to make null,
zero-values and cells having the same value as the cell above invisible by
using backgroundcolor as fontcolor.
Changing the color within vba-code itself is not the problem. But I will
loose information about regular cell-color once I change FontColor. In the
next run it has to be changed back to regular color. I wonder, where
microsoft stores the "regular" coloring of a cell since the conditional
formula only knows the conditional color.

regards
willi

Hi Tom,

thanks a lot. I will store formatting values to be handled by user and then
read these standard options to eventually restore after having assigned
conditional options.

regards
Gulli

"Tom Ogilvy" wrote:

No, excel does not see your code as conditionally formatting the cell. If
you extend your reasoning, then almost any action accomplished which code
that includes an IF statement would be considered condititional formatting.


If you want to restore the color, then you will have to record the original
color.

No provision is made for this.

Perhaps you just want to use

xlAutomatic for font.colorindex
or
xlNone for interior.colorindex

--
Regards,
Tom Ogilvy




"Gulli" wrote:

Hi Tom,
many thanks for response.

excuse me - maybe I don't understand something. The situation is the
following :
first I installed conditional formatting on worksheet level. It works, no
doubt. But as with other stuff like formulas it is a huge job, replacing
changed formulas if you have thousands of cells spread on a certain amount of
worksheets, which do nearly the same things. So I replaced the formulas by
vba-call. Now there is only one formula wich carries out computations on 10
target cells row by row. The cells dont know any formula at all. So far so
good. For performance matters I switch automatic calculations off as well as
screen updating while vba is calculating.
Still for performance reasons I tried to move conditional formatting from
worksheet to vba.
Now I find no property "conditionalFontColor" or something like that. So
coding changes "conditionally" a cell by following code snippet :

..
..
with worksheet.cells(r, z)
if .value = above_value or .value = 0 then
.fontcolor = noshow_value
end if
end with
..
..

but with the next loop ( I changed some value in source-Cell, so
target-Cells have been recomputed ), now the condition ( "if .value =
abbove.value ... ") is not met.
The noshow_color will remain as fontColor - the cell now has different Value
than the cell above and value is < 0, it is still shown with noshow_color,
which is actually the background color.
I added an else to Code like

if .value = above_value or .value = 0 then
.fontcolor = noshow_value
else
.fontcolor = ? 'should_be_normal_fontColor ( which may be different
columnwise )
end if

..
..

This situation is the reason, why I tried to get information, how MS-Excel
keeps the "normal" cell-FontColor between multiple cycles of reformatting
conditionally.

regards
Gulli


"Tom Ogilvy" wrote:

Activecell was use as a representative range object.

Every single cell maintains its colorindex value for both
interior.colorindex and font.colorindex regardless of what color is being
shown by conditional formatting.

If you color a cell red manually, then apply a conditional format that makes
the cells (b9 for example) yellow,

msgbox Range("B9").Interior.ColorIndex

willl display 3 for red.

there is no reason to try to store the original color anywhere, nor is there
any provision for you to do so.

--
Regards,
Tom Ogilvy


"Gulli" wrote:

Hi Tom,

thanks for quick response. Is your suggestion correct only for ActiveCell ?
The problem is, I loop through a matrix of cells starting from an activecell
somewhere else. So all the target cells whose value will conditionally change
won't become active. Depending on input in "Source"Cell the values in
TargetCells will often flip between 0, empty or same Value as cell above and
another value which has to be shown.
Since worksheet.cells(r, c).font.color and .colorindex reflect always the
currently given value and activecell still remains on start-Cell, I found no
location, where the regular fontcolor is stored. Could i use the Color-field
"worksheet.cells(r, c).FormatConditions.Item1.Interior.Color" and store the
normal color ?

regards
gulli


I

"Tom Ogilvy" wrote:

Activecell.Interior.ColorIndex
Activecell.font.colorIndex

never changes regardless of what conditional formatting is displaying.

--
Regards,
Tom Ogilvy


"Gulli" wrote:

Hi anyone,

recently i put all worksheet formula into vba. Application now runs quite
fast except for checking each cell having conditional formula to make null,
zero-values and cells having the same value as the cell above invisible by
using backgroundcolor as fontcolor.
Changing the color within vba-code itself is not the problem. But I will
loose information about regular cell-color once I change FontColor. In the
next run it has to be changed back to regular color. I wonder, where
microsoft stores the "regular" coloring of a cell since the conditional
formula only knows the conditional color.

regards
willi