Solved force cell colour same as another cell

May 15, 2011 at 17:29:39
Specs: Windows XP
Is there a way to dynamically 'force' a cell colour (eg. A1) to be the same as another cell's colour (eg. B1) [where the source cell's colour (B1) could be anything and might change from time to time]? Understand I could simply copy-paste-format but as the 'source' cells will be changing colour regularly don't want to be bound to the manual process but have the colour changes happen automatically.

See More: force cell colour same as another cell

Report •


✔ Best Answer
May 16, 2011 at 19:54:59
I still do not see the relationship between C5 and C12.

What is the logic behind the color of C5?
Why is C5 colored dark blue?

Does C5 need to be a specific type of TEXT string?
Is there something within the TEXT string that denotes it should be Blue?


Apparently all your doing in C12 is cutting out the 18 and subtracting 3 to make it 15.

The only thing I can see is that you need a two digit number in C5 to get any response, else you get a blank cell.

MIKE

http://www.skeptic.com/



#1
May 15, 2011 at 17:48:08
What, if any, is the relationship between the two cells?

Conditional Formatting can possibly do what you wish, but don't have enough info.

MIKE

http://www.skeptic.com/


Report •

#2
May 15, 2011 at 22:53:39
Hi Mike,
The 'source' cells have text which includes numbers at predictable points (eg. referencing " " and "-"); they are assigned colours manually based on content. There are about 15-20 different colours that can be used. The 'forced' cells show differences in the embedded numbers within the source (text-formatted) cells by means of IF, MID, FIND and simple operators. I've just done one formula and copied to all 'forced' cells... just can't think how to "mirror" the colours from the respective source cells dynamically (what i'm trying to say is automatically first-up and in future should the respective source cell colours change).
Clear as mud?
J

Report •

#3
May 16, 2011 at 11:48:29
Maybe an example of your spreadsheet, with some data would help clear things a bit.

But first read this, to learn how to post using PRE tags:

http://www.computing.net/howtos/sho...

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
May 16, 2011 at 17:51:39
Hi Mike,
<pre pre> doesn't really allow me to show an example - can I email you an attachment?
J

Report •

#5
May 16, 2011 at 18:11:38
The objective of the forums is so everyone can learn, would prefer you just post what you can.

MIKE

http://www.skeptic.com/


Report •

#6
May 16, 2011 at 19:23:51
OK. The 'source' cell (say cell C5) might have something like "SE_Equipment_high 18-3" and (manually) coloured dark blue text 2 lighter 40%. The 'forced' cell (say C12) has a formula like this: =IF(ISERROR(MID(C5,FIND("-",C5)-2,FIND("-",C5)-FIND(" ",C5)-1)-MID(C5,FIND("-",C5)+1,100)),"",MID(C5,FIND("-",C5)-2,FIND("-",C5)-FIND(" ",C5)-1)-MID(C5,FIND("-",C5)+1,100)). I would like cell C12 to automatically adopt the colour of cell C5.
[nb. there is a 'grid' of source cells and an equally-sized grid of 'forced' cells]

Report •

#7
May 16, 2011 at 19:54:59
✔ Best Answer
I still do not see the relationship between C5 and C12.

What is the logic behind the color of C5?
Why is C5 colored dark blue?

Does C5 need to be a specific type of TEXT string?
Is there something within the TEXT string that denotes it should be Blue?


Apparently all your doing in C12 is cutting out the 18 and subtracting 3 to make it 15.

The only thing I can see is that you need a two digit number in C5 to get any response, else you get a blank cell.

MIKE

http://www.skeptic.com/


Report •

#8
May 16, 2011 at 21:06:55
Mate,
Sorry to consume your time - seems like we'll not get anywhere on this.

You're right - the formula merely takes the range of the numbers in the source cell. The content (difference) of the cell does not dictate the colour - the colour holds other significance depending on the row and column as well. In other words, the colour is not logically determined by the content of the cells alone but by other factors as well. Because of this I was hoping to find something that adopts the colour of the source cell without any specific conditionalised calc of its own referring to cell content - the colour has qualitative meaning. [eg. was hoping for some means of setting cell B's colour to be the same as cell A's by reference alone and not calculation]. Perhaps it's not possible.

Anyway, I do appreciate your time trying to assist. I will select your last reply as "best answer" presuming it will then stop hassling the forum for a follow up.
Sorry and thanks again.
J


Report •

#9
May 17, 2011 at 03:35:14
It may not be possible with a simple formula,
but you can probably accomplish what you need using some VBA code, unfortunately my coding skill are nil.

Here are a couple of sites that might get you started:

http://www.xldynamic.com/source/xld...

http://www.cpearson.com/excel/color...

MIKE

http://www.skeptic.com/


Report •

#10
May 17, 2011 at 05:06:33
VBA code could be used if there is a consistent relationship between the cells that you want to match.

i.e. C12 must always match C5, C13 must always match C8, etc. - a 1 (or more) to 1 relationship. It can't be C5 sometimes matches C12, sometimes matches C13, etc.

If there is a consistent offset (e.g. C5-C13, C6-C14, C7-C15) then the coding is even easier.

We'd need more specifics before any code could be offered.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
May 22, 2011 at 15:48:37
Thanks to both Mike and DerbyDad03. I retain some hope!

Mike - I will have a look at the VBA links you posted when I'm able. Thanks.

DerbyDad03 - the offset is consistent (C12 will always match C5, D13 - D6, etc.). The text & formula example previously given still apply - there's really not more to it than that. For purposes of flexing your coding muscles (if you want to), the two ranges applicable are C5:AF10 and C12:AF17.

Thanks,
J


Report •

#12
May 22, 2011 at 16:10:54
So I assume you mean that the matching cell will always be 7 rows down in the same column.

Code can be written to match the fill color of C12 with C5, D13 with D6, etc. however, it will not be automatic.

Excel VBA does have a Worksheet_Change event that will run automatically when a worksheet is changed, but unfortunately the changing of a fill color by the user is not considered a "change" in the eyes of VBA.

A button would have to be clicked or a key sequence used or a value in a cell changed in order for the code to run.

If that is still acceptable, I'll throw something together. Let me know.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#13
May 29, 2011 at 15:43:43
Hi DerbyDad03,
Running a macro is a small price to pay for technical wizardry. It may be simple but for someone who doesn't know script it seems pretty clever.
Thanks,
J

Report •

#14
May 29, 2011 at 18:50:44
Run this and each cell in C12:AF17 will be filled with the same colors as C5:AF10 on a cell by cell basis.

In other words, for each cell in C5:AF10, the cell 7 rows below it in the same column will be filled with the color of that cell.

Sub MatchColors()
 For Each myCellColor In Range("C5:AF10")
  myCellColor.Offset(7, 0).Interior.ColorIndex = _
        myCellColor.Interior.ColorIndex
 Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#15
May 29, 2011 at 23:32:23
Thanks! I'll give it a swirl but looks clean so... thanks!

Report •


Ask Question