Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What formula is used for subtracting a range of different cells f. | Excel Worksheet Functions | |||
RE add cells together if value has a formula | Excel Worksheet Functions | |||
How To Use Cells Without Values in a Formula | Excel Worksheet Functions | |||
How do i get a formula for word recognition in cells | Excel Discussion (Misc queries) | |||
Conditional formatting on cells with a VLOOKUP formula in them | Excel Discussion (Misc queries) |