fill red cells included in formula - ajit

A

Ajit Munj

Hi Sir,
I want excel to fill the cells with red colour (fill colour)
which are included in formula e.g.
if a1=sum(b2:b5), b2:b5 should get filled with red colour,
if f1=d1+c5+sum(x1:x5), d1,c5 and x1:x5 should get filled with red colour.
Please guide.
 
B

Bernie Deitrick

Ajit,

Select the cell with the formula, press Ctrl-[, then click your color fill
button.

HTH,
Bernie
MS Excel MVP
 
A

Ajit Munj

Hi
Thanks, It's working, but it does not get updated if I change the formula e.g.
if I add another cell in formula, the added cell do not get filled with
colour? Is
there any way out?

--
Knowldege is Power


Bernie Deitrick said:
Ajit,

Select the cell with the formula, press Ctrl-[, then click your color fill
button.

HTH,
Bernie
MS Excel MVP


Ajit Munj said:
Hi Sir,
I want excel to fill the cells with red colour (fill colour)
which are included in formula e.g.
if a1=sum(b2:b5), b2:b5 should get filled with red colour,
if f1=d1+c5+sum(x1:x5), d1,c5 and x1:x5 should get filled with red colour.
Please guide.
Click to expand...
Click to expand...
 
B

Bernie Deitrick

Ajit,

You could use an event. Copy the code below, right click on your sheet tab,
select "View Code", and paste the code into the window that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewFormula As String
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
If Not Target.HasFormula Then Exit Sub
With Application
.EnableEvents = False
NewFormula = Target.Formula
.Undo
Target.Precedents.Interior.ColorIndex = xlNone
Target.Formula = NewFormula
Target.Precedents.Interior.ColorIndex = 3
.EnableEvents = True
End With
End Sub


Ajit Munj said:
Hi
Thanks, It's working, but it does not get updated if I change the formula e.g.
if I add another cell in formula, the added cell do not get filled with
colour? Is
there any way out?

--
Knowldege is Power


Bernie Deitrick said:
Ajit,

Select the cell with the formula, press Ctrl-[, then click your color fill
button.

HTH,
Bernie
MS Excel MVP


Ajit Munj said:
Hi Sir,
I want excel to fill the cells with red colour (fill colour)
which are included in formula e.g.
if a1=sum(b2:b5), b2:b5 should get filled with red colour,
if f1=d1+c5+sum(x1:x5), d1,c5 and x1:x5 should get filled with red colour.
Please guide.
Click to expand...
Click to expand...
Click to expand...
 
A

Ajit Munj

Thanks Sir, I copied as per your instruction, but it did not work. When do the
change event take effect. I edited the formula with adding one more cell e.g.
=sum(a1:b1) edited like =sum(a1:c1). But the new range did not get filled
with colour. (before editing, I filled the cells involved in formula, with
your Ctrl+[ with red colour).Where did I go wrong?
--
Knowldege is Power


Bernie Deitrick said:
Ajit,

You could use an event. Copy the code below, right click on your sheet tab,
select "View Code", and paste the code into the window that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewFormula As String
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
If Not Target.HasFormula Then Exit Sub
With Application
.EnableEvents = False
NewFormula = Target.Formula
.Undo
Target.Precedents.Interior.ColorIndex = xlNone
Target.Formula = NewFormula
Target.Precedents.Interior.ColorIndex = 3
.EnableEvents = True
End With
End Sub


Ajit Munj said:
Hi
Thanks, It's working, but it does not get updated if I change the formula e.g.
if I add another cell in formula, the added cell do not get filled with
colour? Is
there any way out?

--
Knowldege is Power


Bernie Deitrick said:
Ajit,

Select the cell with the formula, press Ctrl-[, then click your color fill
button.

HTH,
Bernie
MS Excel MVP


Hi Sir,
I want excel to fill the cells with red colour (fill colour)
which are included in formula e.g.
if a1=sum(b2:b5), b2:b5 should get filled with red colour,
if f1=d1+c5+sum(x1:x5), d1,c5 and x1:x5 should get filled with red colour.
Please guide.
Click to expand...
Click to expand...
Click to expand...
 
B

Bernie Deitrick

Ajit,

It worked for me. Send me an email privately, and I will send you a working
version. To email me, take out the spaces and change the dot to .

HTH,
Bernie
MS Excel MVP


Ajit Munj said:
Thanks Sir, I copied as per your instruction, but it did not work. When do the
change event take effect. I edited the formula with adding one more cell e.g.
=sum(a1:b1) edited like =sum(a1:c1). But the new range did not get filled
with colour. (before editing, I filled the cells involved in formula, with
your Ctrl+[ with red colour).Where did I go wrong?
--
Knowldege is Power


Bernie Deitrick said:
Ajit,

You could use an event. Copy the code below, right click on your sheet tab,
select "View Code", and paste the code into the window that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewFormula As String
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
If Not Target.HasFormula Then Exit Sub
With Application
.EnableEvents = False
NewFormula = Target.Formula
.Undo
Target.Precedents.Interior.ColorIndex = xlNone
Target.Formula = NewFormula
Target.Precedents.Interior.ColorIndex = 3
.EnableEvents = True
End With
End Sub


Ajit Munj said:
Hi
Thanks, It's working, but it does not get updated if I change the
Click to expand...
formula
e.g.
if I add another cell in formula, the added cell do not get filled with
colour? Is
there any way out?

--
Knowldege is Power


:

Ajit,

Select the cell with the formula, press Ctrl-[, then click your
Click to expand...
color
fill
button.

HTH,
Bernie
MS Excel MVP


Hi Sir,
I want excel to fill the cells with red colour (fill colour)
which are included in formula e.g.
if a1=sum(b2:b5), b2:b5 should get filled with red colour,
if f1=d1+c5+sum(x1:x5), d1,c5 and x1:x5 should get filled with red colour.
Please guide.
Click to expand...
Click to expand...
Click to expand...
 
H

Hari Prasadh

Hi Bernie,

Hi,

Cant conditional formatting be used here?

Thanks a lot,
Hari
India

Bernie Deitrick said:
Ajit,

It worked for me. Send me an email privately, and I will send you a
working
version. To email me, take out the spaces and change the dot to .

HTH,
Bernie
MS Excel MVP


Ajit Munj said:
Thanks Sir, I copied as per your instruction, but it did not work. When
do the
change event take effect. I edited the formula with adding one more cell e.g.
=sum(a1:b1) edited like =sum(a1:c1). But the new range did not get filled
with colour. (before editing, I filled the cells involved in formula,
with
your Ctrl+[ with red colour).Where did I go wrong?
--
Knowldege is Power


Bernie Deitrick said:
Ajit,

You could use an event. Copy the code below, right click on your sheet tab,
select "View Code", and paste the code into the window that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewFormula As String
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
If Not Target.HasFormula Then Exit Sub
With Application
.EnableEvents = False
NewFormula = Target.Formula
.Undo
Target.Precedents.Interior.ColorIndex = xlNone
Target.Formula = NewFormula
Target.Precedents.Interior.ColorIndex = 3
.EnableEvents = True
End With
End Sub


Hi
Thanks, It's working, but it does not get updated if I change the formula
e.g.
if I add another cell in formula, the added cell do not get filled with
colour? Is
there any way out?

--
Knowldege is Power


:

Ajit,

Select the cell with the formula, press Ctrl-[, then click your color
fill
button.

HTH,
Bernie
MS Excel MVP


Hi Sir,
I want excel to fill the cells with red colour (fill colour)
which are included in formula e.g.
if a1=sum(b2:b5), b2:b5 should get filled with red colour,
if f1=d1+c5+sum(x1:x5), d1,c5 and x1:x5 should get filled with
red
colour.
Please guide.
Click to expand...
Click to expand...
Click to expand...
 
B

Bernie Deitrick

Hari,

I don't think so. Precendent cells can only be found through a manual
procedure or through the use of VBA procedures, not through formulas.

HTH,
Bernie
MS Excel MVP
 
Top