Conditional Formatting based on Colors

  • Thread starter Thread starter cmcfalls
  • Start date Start date
C

cmcfalls

Getteings all...

I have been looking through this forum for wuite some time now for hel
on various Excel problems I encounter, but I have come across one tha
I can not find an answer to...so I ask...

I have several rows of values laid out like a table. I do a lot o
manual checks on this data to verify it. Lets say these columns ar
laid out similar to Amount 1, Amount 2, Amount 3, etc. Well, they ar
calculated off each other, to where if you know key amounts are infac
correct, you can back into a correct assumption that the other value
are also correct.

What I do is check 3-4 key amounts for accuracy and manually shade the
green. This means that a few more, non-adjacent cells, are also correc
(and need to be shaded green).

Is there a way to do a "conditional"-like formatting (possibly using a
If statement) that would say something to the effect of If CellXX i
green in color then color this cell (whichever the formula is in) gree
also?

Thanks in advance all..
 
Select all the cells, use Format>Conditional Formatting, change Condition 1
to Formula Is and a formula of (for instance) =$A1=17, and set the pattern
colour.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
You misunderstood...I am trying to do a conditional formatting on th
color, not the value. In your example, what I am trying to do is sa
"If Other Cell Formatting is [COLOR], Format This Cell to [COLOR]
 
Why is that particular cell green? The use the same condition
for a conditional format. If there is no reason that can be sought
you need VBA

http://www.cpearson.com/excel/colors.htm

you cannot refer to UDFs directly so you need to create a help range
and then refer to that in the conditional formatting
 
I sort of understood you, but I was working on the basis that if one cell
was shaded then others would (or could) also be (from this statement '...you
can back into a correct assumption that the other values are also correct
....'). I also assumed, perhaps mistakenly, that the original manual shading
could be automated. Is this not so?

Carrying on and assuming that the answer to the last question is no, then
you need to create a UDF to get that colour. That bit is easy. Problem that
I see here is that I assume (lots of that going on) that the target cell has
no fixed relationship to the source cell, that is the source cell is not
always fixed , even in row or column terms.

Post back, but I'll have a think in the meantime.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
In summary, I am lazy. I do not want (If I can get around it) to have t
manually do all the shading. And the original, manual shading HAS to b
done manually because I check my worksheet numbers against a pape
copy. The thing is, it is not always the same number that I a
checking. For example, (as very simple example), if my columns wer
Sale Price, City Taxes, State Taxes, Shipping / Handling, and Net Sal
Price, I might check against Net Sale Price and one of the Taxes, or
might check the S&H and Taxes, etc. So that HAS to be manual...no wa
around it.

As for the reason I want the automatic shading of the others, I als
have a total for each column. Once I have checked (and shaded)
certain percentage of the columns, the total for that column i
accepted as "correct". This is a MASSIVE worksheet and I do not want t
have to count each cell that I have shaded green in order to "complete
the worksheet. I guess I could use a countif function, but that onl
works for the one column when I am trying to do this across many..
 
I setup a little test on a 10x20 array of cells, and put it a CF that
conditionally coloured An:Jn cells if any other cell in An:Jn is manually
coloured (you can actually have 2 colours, a manual colour and a CF colour,
although obviously only one shows). Unfortunately, it was slow, and so I
hate to think what would happen on your thousands of rows.

I'll continue looking in the spare moments.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top