Conditional Formatting

[Adam Ronalds]

How can I use the formula function in conditional formatting in order to
indicate if a formula in the cell has been altered?

 

[Dave]

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.

 

[Adam Ronalds]

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]

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.

 

[Adam Ronalds]

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]

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.

 

[Adam Ronalds]

thanks, cells (p8:am90)

Adam

 

[Dave]

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.

 

[Adam Ronalds]

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]

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.

 

[Adam Ronalds]

thanks