fill red cells included in formula - ajit

[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.

--
Knowldege is Power

[Bernie Deitrick]

Ajit,

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

HTH,
Bernie
MS Excel MVP


"Ajit Munj" <[email protected]> wrote in message
news:[email protected]...
> 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 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" wrote:

> Ajit,
>
> Select the cell with the formula, press Ctrl-[, then click your color fill
> button.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Ajit Munj" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 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" 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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 Prasadh]

Hi Bernie,

Hi,

Cant conditional formatting be used here?

Thanks a lot,
Hari
India

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

[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

> Hi Bernie,
>
> Hi,
>
> Cant conditional formatting be used here?
>
> Thanks a lot,
> Hari
> India