If a cell is updated can several other cells change colour?

[IanClegg]

Hi,
Can anyone help me with this one?
If a cell is updated can several other cells change colour?
eg. If a particular cell is changed, I want several other cells to be
highlighted by changing their colour

Is there a simple way of doing this?

 

[Warren Easton]

Hi ian,

You can do this by contitionally formatting your cell.
This can change the colour of your font when you type in the required value
into a cell.

 

[Chris J Dixon]

Hi,
Can anyone help me with this one?
If a cell is updated can several other cells change colour?
eg. If a particular cell is changed, I want several other cells to be
highlighted by changing their colour

Is there a simple way of doing this?

Let us say that you want cell A2 to turn green if cell M2 has a
number entered

Put the cursor in A2, and use conditional formatting. Instead of
"Cell value is" select "Formula is" and for a condition enter
"=M2>0" (without the quote marks) Then choose the format you
want to apply.

Use the format painter to copy down the A column.

If you want to use the same format for other columns, then make
the formula "=$M2>0" and you can copy the format to adjacent
columns.

Beware when copying the format that it will overwrite any
existing formatting.

Chris

 

[IanClegg]

But I want other cells to change colour if any change is made to another cell

eg. if cell Z2 is changed, I want cells A2, B2, C2, D2, M2, Y2 and Z2 to be
displayed in red

 

[Don Guillett]

If you want a particular value use format>conditional formatting>>>>
If you want to change based on ANY change use a worksheet_change event macro
restricted to the cell desired.

 

[IanClegg]

Almost what I want, but it needs to happen if any change at all is made to
the original cell and I don't know how to define that in a formula

 

[IanClegg]

Hi Don,
Do you have an example of this that I could use as a base ?

 

[Thomas [PBD]]

IanClegg,

You can try this code into the Sheet which you are wanting to change. I
assumed three cells to highlight. I must note that I didn't put any coding
in here to unhighlight to cells, so they will currently stay highlighted
until you unhighlight them.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Chng_cell As String
Dim highlight1 As String
Dim highlight2 As String
Dim highlight3 As String

'Change these as required
Chng_cell = "$A$1"
highlight1 = "$B$1"
highlight2 = "$B$2"
highlight3 = "$B$3"

If Target.Address = Chng_cell Then
'Highlighted Cell 1
With Range(highlight1).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
'Highlighted Cell 2
With Range(highlight2).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
'Highlighted Cell 3
With Range(highlight3).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If
End Sub

 

[Don Guillett]

Right click sheet tab>view code>insert this. However, it will change the
color with ANY change to the same color. Is this what you want?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then _
Range("a2,c4,d5").Interior.ColorIndex = 6
End Sub

 

[Don Guillett]

Mine is a bit more compact.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

IanClegg,

You can try this code into the Sheet which you are wanting to change. I
assumed three cells to highlight. I must note that I didn't put any
coding
in here to unhighlight to cells, so they will currently stay highlighted
until you unhighlight them.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Chng_cell As String
Dim highlight1 As String
Dim highlight2 As String
Dim highlight3 As String

'Change these as required
Chng_cell = "$A$1"
highlight1 = "$B$1"
highlight2 = "$B$2"
highlight3 = "$B$3"

If Target.Address = Chng_cell Then
'Highlighted Cell 1
With Range(highlight1).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
'Highlighted Cell 2
With Range(highlight2).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
'Highlighted Cell 3
With Range(highlight3).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If
End Sub

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.

Hi,
Can anyone help me with this one?
If a cell is updated can several other cells change colour?
eg. If a particular cell is changed, I want several other cells to be
highlighted by changing their colour

Is there a simple way of doing this?

 

[Thomas [PBD]]

I see that. I wrote mine as you were writing yours I see. I think I would
use yours instead.

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.

Mine is a bit more compact.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

IanClegg,

You can try this code into the Sheet which you are wanting to change. I
assumed three cells to highlight. I must note that I didn't put any
coding
in here to unhighlight to cells, so they will currently stay highlighted
until you unhighlight them.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Chng_cell As String
Dim highlight1 As String
Dim highlight2 As String
Dim highlight3 As String

'Change these as required
Chng_cell = "$A$1"
highlight1 = "$B$1"
highlight2 = "$B$2"
highlight3 = "$B$3"

If Target.Address = Chng_cell Then
'Highlighted Cell 1
With Range(highlight1).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
'Highlighted Cell 2
With Range(highlight2).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
'Highlighted Cell 3
With Range(highlight3).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If
End Sub

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.

Hi,
Can anyone help me with this one?
If a cell is updated can several other cells change colour?
eg. If a particular cell is changed, I want several other cells to be
highlighted by changing their colour

Is there a simple way of doing this?