Conditional formatting

[glen.e.mettler]

I have detailed data in worksheet "Proposal Status" which are color
coded (ie background color is red, green, yellow, blue).

In worksheet "Summary" I have the following formula in various cells
that pulls data from "Proposal Status" (every 5th row)

=OFFSET('Proposal Status'!$G$5,ROW(G1)*5-5,0)

This does fine on capturing the data - I would like to capture the
color code as well as the data.

Is that possible?

Glen

 

[Kletcho]

What do you plan on doing with the bg color? Do you plan on making the
cell have same formatting as the other sheet and why? You may be able
to acheive this same result easier through using autofilters and having
a helper column on the side saying whether or not it is a 5th row.

 

[Glen Mettler]

The background color in the original sheet is changed by the user via a menu
option tied to a macro.
The summary sheet is a presentation sheet. I need to collect whatever
background color is in the original sheet.
The 5th row contains 7 columns that can have four colors - green, yellow,
red, or blue - I need to capture that color along with the data. I have a
formula for the data, but I can't seem to get the color.


Glen

 

[Kletcho]

The only way to do things according to format is through VBA. Try this
in the sheet module of your "presentation sheet" and put it in the
worksheet activate event:

Dim rng As Range

For Each rng In Worksheets("Presentation").UsedRange
With rng
.ColorIndex = Worksheets("Proposal Status").Cells((rng.Row
* 5) - 5, rng.Column).Interior.ColorIndex
.Pattern = xlSolid
End With
Next rng

This is untested so let me know if you have troubles with it.