Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Dana Zulager
 
Posts: n/a
Default copy conditional format as ACTUAL format

i'd like to figure out how i can copy a group of cells that have a
conditional format (stoplight style ... color pattern highlighted for a
certain condition) and paste special only the format, but not keep the
conditional format rules.

one time that it is necessary is if i have an analysis spreadsheet with all
my rules and formulas and internals, but i produce an "output" spreadsheet
that only shows values, widths and formats. the conditional format for a
particular column depends on the values of a column which i don't even
include in the output sheet, so the conditional formatting rules don't work
as designed once transformed to the "output" sheet.

hopefully this question makes sense! thanks for any tips, pointers, ideas.
  #2   Report Post  
Dana Zulager
 
Posts: n/a
Default

ok, good stuff from Pearson (i've been poking around)

knowing next-to-nothing about adding my own functions using VBA, i added the
functions he shows for determining the RGB color components or color code for
a conditional format (www.cpearson.com/excel/CFcolors.htm) ... and i
successfully return the color codes for my conditional formats ...
[ColorIndexofCF(A1,False), ColorofCF(A1,False)]

now how do i apply that color to another cell?? my Excel help functionality
is on-the-fritz, so i'll hunt there and elsewhere.

again, any tips, pointers, etc. are appreciated! ... dana

"Dana Zulager" wrote:

i'd like to figure out how i can copy a group of cells that have a
conditional format (stoplight style ... color pattern highlighted for a
certain condition) and paste special only the format, but not keep the
conditional format rules.

one time that it is necessary is if i have an analysis spreadsheet with all
my rules and formulas and internals, but i produce an "output" spreadsheet
that only shows values, widths and formats. the conditional format for a
particular column depends on the values of a column which i don't even
include in the output sheet, so the conditional formatting rules don't work
as designed once transformed to the "output" sheet.

hopefully this question makes sense! thanks for any tips, pointers, ideas.

  #3   Report Post  
Bob Phillips
 
Posts: n/a
Default

Dana,

This can be done, but it would mean examining each condition and testing if
the cell meets that condition and extracting all formatting. A lot of
effort, do you want to go through that?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dana Zulager" wrote in message
...
i'd like to figure out how i can copy a group of cells that have a
conditional format (stoplight style ... color pattern highlighted for a
certain condition) and paste special only the format, but not keep the
conditional format rules.

one time that it is necessary is if i have an analysis spreadsheet with

all
my rules and formulas and internals, but i produce an "output" spreadsheet
that only shows values, widths and formats. the conditional format for a
particular column depends on the values of a column which i don't even
include in the output sheet, so the conditional formatting rules don't

work
as designed once transformed to the "output" sheet.

hopefully this question makes sense! thanks for any tips, pointers, ideas.



  #4   Report Post  
Dana Zulager
 
Posts: n/a
Default

actually, using Pearson's VBA functions [ColorofCF, ColorIndexofCF] i can in
one function extract the color or color index of the conditionally-formatted
cells (i am only applying a color, nothing else) ... so all i need to know
now is how to "apply" that color code to another cell ... ???

that's all i want to do is grab the conditionally-formatted color (if there
is one) and apply it to a cell with no conditional format applied.

.... dana

"Bob Phillips" wrote:

Dana,

This can be done, but it would mean examining each condition and testing if
the cell meets that condition and extracting all formatting. A lot of
effort, do you want to go through that?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dana Zulager" wrote in message
...
i'd like to figure out how i can copy a group of cells that have a
conditional format (stoplight style ... color pattern highlighted for a
certain condition) and paste special only the format, but not keep the
conditional format rules.

one time that it is necessary is if i have an analysis spreadsheet with

all
my rules and formulas and internals, but i produce an "output" spreadsheet
that only shows values, widths and formats. the conditional format for a
particular column depends on the values of a column which i don't even
include in the output sheet, so the conditional formatting rules don't

work
as designed once transformed to the "output" sheet.

hopefully this question makes sense! thanks for any tips, pointers, ideas.




  #5   Report Post  
Bob Phillips
 
Posts: n/a
Default

So if you know the colorindex, it is simply

Activcell.Interior.Colorindex = nColorIndex

where nColorIndex is that extarcted colour.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dana Zulager" wrote in message
...
actually, using Pearson's VBA functions [ColorofCF, ColorIndexofCF] i can

in
one function extract the color or color index of the

conditionally-formatted
cells (i am only applying a color, nothing else) ... so all i need to know
now is how to "apply" that color code to another cell ... ???

that's all i want to do is grab the conditionally-formatted color (if

there
is one) and apply it to a cell with no conditional format applied.

... dana

"Bob Phillips" wrote:

Dana,

This can be done, but it would mean examining each condition and testing

if
the cell meets that condition and extracting all formatting. A lot of
effort, do you want to go through that?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dana Zulager" wrote in message
...
i'd like to figure out how i can copy a group of cells that have a
conditional format (stoplight style ... color pattern highlighted for

a
certain condition) and paste special only the format, but not keep the
conditional format rules.

one time that it is necessary is if i have an analysis spreadsheet

with
all
my rules and formulas and internals, but i produce an "output"

spreadsheet
that only shows values, widths and formats. the conditional format for

a
particular column depends on the values of a column which i don't even
include in the output sheet, so the conditional formatting rules don't

work
as designed once transformed to the "output" sheet.

hopefully this question makes sense! thanks for any tips, pointers,

ideas.







  #6   Report Post  
Jim May
 
Posts: n/a
Default

Bob,
So why is it that the below doesn't work (Get R/T 424 - Object required)?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Activcell.Interior.ColorIndex = 6
End Sub

Thanks,
Jim

"Bob Phillips" wrote in message
...
So if you know the colorindex, it is simply

Activcell.Interior.Colorindex = nColorIndex

where nColorIndex is that extarcted colour.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dana Zulager" wrote in message
...
actually, using Pearson's VBA functions [ColorofCF, ColorIndexofCF] i

can
in
one function extract the color or color index of the

conditionally-formatted
cells (i am only applying a color, nothing else) ... so all i need to

know
now is how to "apply" that color code to another cell ... ???

that's all i want to do is grab the conditionally-formatted color (if

there
is one) and apply it to a cell with no conditional format applied.

... dana

"Bob Phillips" wrote:

Dana,

This can be done, but it would mean examining each condition and

testing
if
the cell meets that condition and extracting all formatting. A lot of
effort, do you want to go through that?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dana Zulager" wrote in

message
...
i'd like to figure out how i can copy a group of cells that have a
conditional format (stoplight style ... color pattern highlighted

for
a
certain condition) and paste special only the format, but not keep

the
conditional format rules.

one time that it is necessary is if i have an analysis spreadsheet

with
all
my rules and formulas and internals, but i produce an "output"

spreadsheet
that only shows values, widths and formats. the conditional format

for
a
particular column depends on the values of a column which i don't

even
include in the output sheet, so the conditional formatting rules

don't
work
as designed once transformed to the "output" sheet.

hopefully this question makes sense! thanks for any tips, pointers,

ideas.







  #7   Report Post  
Norman Jones
 
Posts: n/a
Default

Hi Jim,


So why is it that the below doesn't work


Activcell. is missing an 'e' before the 'c'

You might also wish to look at:

http://www.cpearson.com/excel/excelM...ightActiveCell
and
http://www.cpearson.com/excel/RowLiner.htm


---
Regards,
Norman



"Jim May" wrote in message
news:B%gtd.1021$ln.998@lakeread06...
Bob,
So why is it that the below doesn't work (Get R/T 424 - Object required)?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Activcell.Interior.ColorIndex = 6
End Sub

Thanks,
Jim

"Bob Phillips" wrote in message
...
So if you know the colorindex, it is simply

Activcell.Interior.Colorindex = nColorIndex

where nColorIndex is that extarcted colour.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dana Zulager" wrote in message
...
actually, using Pearson's VBA functions [ColorofCF, ColorIndexofCF] i

can
in
one function extract the color or color index of the

conditionally-formatted
cells (i am only applying a color, nothing else) ... so all i need to

know
now is how to "apply" that color code to another cell ... ???

that's all i want to do is grab the conditionally-formatted color (if

there
is one) and apply it to a cell with no conditional format applied.

... dana

"Bob Phillips" wrote:

Dana,

This can be done, but it would mean examining each condition and

testing
if
the cell meets that condition and extracting all formatting. A lot of
effort, do you want to go through that?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dana Zulager" wrote in

message
...
i'd like to figure out how i can copy a group of cells that have a
conditional format (stoplight style ... color pattern highlighted

for
a
certain condition) and paste special only the format, but not keep

the
conditional format rules.

one time that it is necessary is if i have an analysis spreadsheet

with
all
my rules and formulas and internals, but i produce an "output"

spreadsheet
that only shows values, widths and formats. the conditional format

for
a
particular column depends on the values of a column which i don't

even
include in the output sheet, so the conditional formatting rules

don't
work
as designed once transformed to the "output" sheet.

hopefully this question makes sense! thanks for any tips, pointers,

ideas.









  #8   Report Post  
Dave Peterson
 
Posts: n/a
Default

You may want to take a look at this similar thread:
http://groups-beta.google.com/group/...768bb674d58088
(one line in your browser)

You may want to look at the way Ashish Chamaria did it using MSWord, too. (in
that same thread)

Dana Zulager wrote:

ok, good stuff from Pearson (i've been poking around)

knowing next-to-nothing about adding my own functions using VBA, i added the
functions he shows for determining the RGB color components or color code for
a conditional format (www.cpearson.com/excel/CFcolors.htm) ... and i
successfully return the color codes for my conditional formats ...
[ColorIndexofCF(A1,False), ColorofCF(A1,False)]

now how do i apply that color to another cell?? my Excel help functionality
is on-the-fritz, so i'll hunt there and elsewhere.

again, any tips, pointers, etc. are appreciated! ... dana

"Dana Zulager" wrote:

i'd like to figure out how i can copy a group of cells that have a
conditional format (stoplight style ... color pattern highlighted for a
certain condition) and paste special only the format, but not keep the
conditional format rules.

one time that it is necessary is if i have an analysis spreadsheet with all
my rules and formulas and internals, but i produce an "output" spreadsheet
that only shows values, widths and formats. the conditional format for a
particular column depends on the values of a column which i don't even
include in the output sheet, so the conditional formatting rules don't work
as designed once transformed to the "output" sheet.

hopefully this question makes sense! thanks for any tips, pointers, ideas.


--

Dave Peterson
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
conditional format of data tables in charts [email protected] Charts and Charting in Excel 2 January 25th 05 04:56 PM
Problem with Conditional format deletion [email protected] Excel Discussion (Misc queries) 3 December 13th 04 06:10 PM
deleting a conditional format [email protected] Excel Discussion (Misc queries) 4 December 7th 04 11:45 PM
make a conditional format the default Fred Evans Excel Discussion (Misc queries) 3 December 6th 04 06:01 AM
Conditional format rexmann Excel Discussion (Misc queries) 4 December 2nd 04 01:01 PM


All times are GMT +1. The time now is 11:32 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"