Cell Fill Colors between Worksheets

  • Thread starter Thread starter Marti
  • Start date Start date
M

Marti

Let's say sheet #1 has a blue fill in a certain cell and
a value of let's say someone's name. I have a formula on
sheet two that brings the name into sheet #2.....
But how do I (Can I?) get the fill to come into sheet #2
also....
The fill color and name change on occasion and I need
sheet #2,3,4,etc. to be able to change the value and the
color fill at the same time....
Possible? Please Help!:)
 
Can't be done with worksheet functions, which can return values, not
change properties like cell color.

You could use a Worksheet_Activate() event macro. Put this in the
second worksheet code module (right-click on the worksheet tab,
choose View Code, paste the code in the window that opens, then
click the XL icon on the toolbar to return to XL). Say the certain
cell is Sheet1!A1 and on sheet2, there's a reference to it in cell
J1 with the formula =Sheet1!A1:

Private Sub Worksheet_Activate()
Range("J1").Interior.ColorIndex = _
Sheets("Sheet1").Range("A1").Interior.ColorIndex
End Sub

Note that this changes the cell's color only when the worksheet is
activated - presumably as long as the values changes, you're not
going to see the color change until you switch worksheets. The
exception to this, of course, is if you are printing out sheets
without selecting them.
 
Hi Marti,
You cannot do what you ask with builtin Worksheet Functions.
Don't know what you have for a formula to pull in the name
from Sheet1, but the following may get you started though
since this will not be automatic you might want to rethink
your dependence on color. In the example below
the argument to showColorIndex must be a cell address,
it cannot be be a VLOOKUP formula, for instance.

Function showColorIndex(rcell)
showColorIndex = rcell.Interior.ColorIndex
End Function

A2: =Sheet1!A5
B2: =showColorIndex(Sheet1!A5)

Select Column A and run the following macro

Sub ColorFromRight()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Cell As Range
Selection.Interior.ColorIndex = xlNone 'clear existing color
' On Error Resume Next 'In case no cells in selection
For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
If IsNumeric(Cell.Offset(0, 1).Value) _
And Cell.Offset(0, 1).Value <= 56 Then
Cell.Interior.ColorIndex = Cell.Offset(0, 1).Value
End If
Next Cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

If you are new to macros, see my page
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top