fill red cells included in formula - ajit
 

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.

--
Knowldege is Power

Ajit,

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

HTH,
Bernie
MS Excel MVP


"Ajit Munj" wrote in message
...
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.

--
Knowldege is Power

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" wrote:

Ajit,

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

HTH,
Bernie
MS Excel MVP


"Ajit Munj" wrote in message
...
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.

--
Knowldege is Power

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" wrote in message
...
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" wrote:

Ajit,

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

HTH,
Bernie
MS Excel MVP


"Ajit Munj" wrote in message
...
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.

--
Knowldege is Power

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" wrote:

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" wrote in message
...
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" wrote:

Ajit,

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

HTH,
Bernie
MS Excel MVP


"Ajit Munj" wrote in message
...
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.

--
Knowldege is Power

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

Ajit,

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

HTH,
Bernie
MS Excel MVP


"Ajit Munj" wrote in message
...
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.

--
Knowldege is Power

Hi Bernie,

Hi,

Cant conditional formatting be used here?

Thanks a lot,
Hari
India

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

Ajit,

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

HTH,
Bernie
MS Excel MVP


"Ajit Munj" wrote in message
...
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.

--
Knowldege is Power

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

Hi Bernie,

Hi,

Cant conditional formatting be used here?

Thanks a lot,
Hari
India