If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional Formatting
How can I use the formula function in conditional formatting in order to
indicate if a formula in the cell has been altered? |
#2
|
|||
|
|||
Conditional Formatting
Hi,
Conditional formatting alone can not do this for you. You would need an change event macro. Assuming G1 is the cell you want to check, and =G2+G3 is the formula you want unchanged in G1, try something like: Private Sub Worksheet_Change(ByVal Target As Range) If Range("G1").Formula "=G2+G3" Then MsgBox "Formula in G1 is incorrect" End Sub Change references and the desired formula to suit your sheet. You could put anything you like after 'Then' I've put in a message box. Regards - Dave. |
#3
|
|||
|
|||
Conditional Formatting
thank you. I want to allow the cells to be changed but want to be able to
see if someone changed them. Specifically, I'm forcasting results based on trends but, want salepeople to be able to change the forecasted trends if necessary. If they make a change in a cell with the forecasted formula, I want to quickly be able to see what cells they changed (or hardcoded in their number). I think that your macro would work at limiting a user from making a change in the formula but, I actually want them to make changes in the formula'd cells but see where they made changes quickly. Is there a possible solution for this request? Thanks. Adam "Dave" wrote: Hi, Conditional formatting alone can not do this for you. You would need an change event macro. Assuming G1 is the cell you want to check, and =G2+G3 is the formula you want unchanged in G1, try something like: Private Sub Worksheet_Change(ByVal Target As Range) If Range("G1").Formula "=G2+G3" Then MsgBox "Formula in G1 is incorrect" End Sub Change references and the desired formula to suit your sheet. You could put anything you like after 'Then' I've put in a message box. Regards - Dave. |
#4
|
|||
|
|||
Conditional Formatting
OK, what about:
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(ActiveCell, Range("G1")) Is Nothing Then Range("G1").Font.ColorIndex = 3 Range("G1").Font.Bold = True End If End Sub This will allow a change in G1, but apply red bold when it is. Any good? Regards - Dave. |
#5
|
|||
|
|||
Conditional Formatting
WOW, that is good. only 2 more quick questions: (1) how do I do that for a
range of cells? and (2) how do I do it so that it only highlights in red font if the formula is replaced by a hard coded number? (I have a drop down reference that allows the forecast formula results to change but I don't want that to affect the font, I only want a hard coded input to affect the font). Thanks! Adam "Dave" wrote: OK, what about: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(ActiveCell, Range("G1")) Is Nothing Then Range("G1").Font.ColorIndex = 3 Range("G1").Font.Bold = True End If End Sub This will allow a change in G1, but apply red bold when it is. Any good? Regards - Dave. |
#6
|
|||
|
|||
Conditional Formatting
Hi,
Second question first: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(ActiveCell, Range("G1")) Is Nothing Then If Not Range("G1").HasFormula Then Range("G1").Font.ColorIndex = 3 Range("G1").Font.Bold = True End If End If End Sub First question: what range do you want? How many cells in your range? Regards - Dave. |
#7
|
|||
|
|||
Conditional Formatting
thanks, cells (p8:am90)
Adam "Dave" wrote: Hi, Second question first: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(ActiveCell, Range("G1")) Is Nothing Then If Not Range("G1").HasFormula Then Range("G1").Font.ColorIndex = 3 Range("G1").Font.Bold = True End If End If End Sub First question: what range do you want? How many cells in your range? Regards - Dave. |
#8
|
|||
|
|||
Conditional Formatting
P8:AM90!? Thats a lot of cells that could have formulas. Anyhoo..
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("P8:AM90")) Is Nothing Then If Not Target.Cells.HasFormula Then Target.Cells.Font.ColorIndex = 3 Target.Cells.Font.Bold = True End If End If End Sub Does this do what you want? If you want to remove the red bold when a formula is re-entered: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("P8:AM90")) Is Nothing Then If Not Target.Cells.HasFormula Then Target.Cells.Font.ColorIndex = 3 Target.Cells.Font.Bold = True End If If Target.Cells.HasFormula Then Target.Cells.Font.ColorIndex = 1 Target.Cells.Font.Bold = False End If End If End Sub Regards - Dave. |
#9
|
|||
|
|||
Conditional Formatting
awesome, thanks! BTW, what's the best VBA training book to buy for an expert
in excel but a nimrod in VBA? Thanks! Adam "Dave" wrote: P8:AM90!? Thats a lot of cells that could have formulas. Anyhoo.. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("P8:AM90")) Is Nothing Then If Not Target.Cells.HasFormula Then Target.Cells.Font.ColorIndex = 3 Target.Cells.Font.Bold = True End If End If End Sub Does this do what you want? If you want to remove the red bold when a formula is re-entered: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("P8:AM90")) Is Nothing Then If Not Target.Cells.HasFormula Then Target.Cells.Font.ColorIndex = 3 Target.Cells.Font.Bold = True End If If Target.Cells.HasFormula Then Target.Cells.Font.ColorIndex = 1 Target.Cells.Font.Bold = False End If End If End Sub Regards - Dave. |
#10
|
|||
|
|||
Conditional Formatting
Hi,
I've bought XL2000 VBA Programming for Dummies, which I have found very good. But I've learnt at least as much, by using the macro recorder, and then mulling over the code produced by XL. But it does take time. Regards - Dave. |
|
Thread Tools | |
Display Modes | |
|
|