Excel 2007 - Copy & Paste Only Fill Colur 4

[greengo204]

Not sure if this is possible but is there a way to copy any paste just the fill colour of a cell?

I've tried "copy & paste special > formats" but that paste all the cells formatting. I am specifically looking for just the fill colour. If this can't be done with the usual features i would be happy with a VBA solution.

Regards,

 

[BizDev]

Could a format validation serve your needs here?

 

[greengo204]

Possible i have been attempting that method unsuccessfully.

Additional info:

I am trying to fill a cell conditionally with colour is the cell contains a formula.

I have been using a custom function (VBA) to identify if the relevant cell has a formula and then coditionally format the cell if the formula returns "TRUE". I was hoping to paste the colours over the origanal cells that contain the formula. But this method has flaws as you cant conp and paste conditional formatted colours as values.

any suggestions?

 

[SkipVought]

Hi,

Rather than tell us HOW you want to accomplish (copy 'n' paste interior color), please tell us functionally WHAT you are trying to accomplish, like when the data in the row means this, then make this happen.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[greengo204]

fair point.

i am trying to conditionally format a cell (fill colour) if the cell contains any formula.

any suggestions?

 

[SkipVought]

The Conditional Format FEATURE in Excel uses DATA VALUES to make that sort of determination.

If you want to test for the presence of a FORMULA, you can use one of the IS functions. ISREF() is the logical choice, although I have not achieved consistent results. Also check out ISNUMBER() and ISTEXT(). Do you CF, based on the outcome of these functions.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[greengo204]

I've tried all three 'IS' functions unsuccessfully, the 'ISREF' funtions conditional formatting always returned TRUE?

I've found a macro which is doing the job, but cant help thinking theres an easier method...

Macro:

http://www.experts-exchange.com/Sof...Office_Suites/MS_Office/Excel/Q_23812431.html

 

[xlhelp]

Tested in Excel 2007.

Try this:
Under formulas tab, click on define name.
In the refers to box put =GET.CELL(6,Sheet1!$A1)
where A1 is the first cell in your Conditional formatting range.

and in the name box type in some sort of description like FormIs. Click OK

Select A1:A3 (where one of the cells contains a formula)with A1 as the active cell, under the Home tab go to conditional formatting, New Rule.

select "Use a formula to determine which cell to format"
and for "Format Values where this formula is true"
and type in =LEFT(FormIs)="="

and select your format

A man has only two choices: He can be right or he can be happy.

 

[PeterMoran]

Hi,

I have an Excel AddInn called "Paste Special Plus" which provides a further 22 options beyond Excel's Paste Special facility, including just copy the Fill Colour.

It is from Andrew Engwirda's site:

http://blog.livedoor.jp/andrewe/

You will see it on the left under Tips and Dowloads.

Good Luck!

Peter Moran

 

[greengo204]

xlhelp: Thanks thats what i was looking for.

Petermoran: Cheers i will look into that plugin as im sure i may have other uses for it.

Regards,

 

[Salut39]

I am trying to conditionally format a cell (fill colour) if the cell contains any formula."

CNTR + G,
Special...,
Formulas, and then choose the colour from formats.

Yuri

 

[lionelhill]

This is perhaps also the place to mention again the barely-known key-combination ctrl-` (top left key on my UK keyboard), which toggles between displaying values and showing formulae. Doesn't do what you asked, but it does show at a glance which cells are formulae, if that's the underlying need.