Can I copy color with an equals sign?

**rohed** · 03-07-2007, 05:37 PM

If I have a cell B5 that contains: =A1
This puts the value of A1 into B5.
If A1 is red, can I make B5 copy that redness? Is there an easy way to make it copy the color or format, not just the value, using a function? I can't find this in my excel manual.

**mudraker** · 03-07-2007, 07:06 PM

No you can not

**rohed** · 03-07-2007, 07:55 PM

Is there a way for cell to change color when it gets a certain input,
sort of like =if(x=5,turn red, don't change)?

**CHAStats** · 03-07-2007, 08:04 PM

Try Conditional Formatting as a Formatting Tool within Excel.

Format>Conditional Formatting...

Enter your values (ie 5 ... from your example) and then click on the Format button (bottom right) and you will be able to change background to red and text to bold-black or white. Whatever colour scheme works for you.

I've used this lots when I need to highlight lines that are, for example, below 10% or over 90% with different colors, and other things too...

Hope this helps.

CHA Stats

**rohed** · 03-08-2007, 12:41 AM

I had so much fun playing with conditional formating, forgot to say thanks MUDRAKER & CHA Stats.

Still trying to trick it (cell-C) into acting like a function that pulls in the color with the value.

Best I can do is get the cell next to cell-C to pull in the color by using to duplicate cells with conditional formatting and cell-B looking at the value of cell-A. Cell-B has the font color change with backround color so that value is invisible. Problem is you only have 3 choices?

**robert111** · 03-08-2007, 10:26 AM

there is a way to have unlimited conditional formatting, at least 20, it is a bit of code in these archives i think, but google it.

**rohed** · 03-09-2007, 02:51 PM

thanks, How about a value that triggers a macro that goes and gets the format in question puts it in the correct cell. Can I do that if I dig into macros more?

I haven't played with macros enough to know but it sounds like a security issue. (hmm, date/time triggers macro and excel macro takes over computer which takes over the internet which takes over the world and ... ?)

**mudraker** · 03-09-2007, 05:19 PM

You could use a sheet change event macro

To install macro to correct location

Copy this macro
GoTo Excel
Select sheet this is to appy to
Right Click on Sheet Name Tab > select View Code
Paste macro into the Worksheet Module displayed

example of worksheet change macro
This example works only on cells a1 & b1

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Rng As Range

   For Each Rng In Target
      If Not Application.Intersect(Rng, Range("a1:b1")) Is Nothing Then
         Application.EnableEvents = False
         With Rng.Interior
            .Pattern = xlSolid
            Select Case Rng.Value
            Case 5
               .ColorIndex = 3
            Case 4
               .ColorIndex = 6
            Case Else
               .ColorIndex = xlNone
            End Select
         End With
      End If
   Next Rng
   Application.EnableEvents = True
End Sub