OK Stevie,Bob,
Thanks for your help so far.
Now i have the module loaded which and have applied the cf. However it
doesn't seem to work they way I need.
I tried the first function
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$1" Then
If Target.HasFormula Then
Target.Interior.Color = RGB(255, 0, 0)
Else
Target.Interior.Color = RGB(0, 255, 0)
End If
End If
End Sub
and it worked to a point for 1 cell.
I removed the line
If Target.Address = "$A$1" Then
and the corresponding "end if" and it applied the function to the whole
worksheet, not just the columns i need.
The other thing is that this function only takes effect as a cell is
changed, and it would be better if it could apply to predefined
formulas/values already in cells.
So
1. Can I apply the above so it will only work on the specific cells/columns
without having to copy the function for each cell.
2. Can it then be applied to predetermined Values/formulas in the cells.
If you need further info re the intricacies of this worksheet, and the
reason for my needs. please email me
I look forward to your reply and your assistance.
Thanks.
Dave.
"Stevie_mac" wrote:

There is a better way that will solve all your problems...

* Open VB (Alt+F11)
* Add a Module
* Paste the following function into the new module
Public Function HasFormula(target As Range) As Boolean
HasFormula = target.HasFormula()
End Function

Now you can use conditional formatting
Example...
Select cell B1
Open Conditional Formatting
In Condition1, select [Formula Is]
Enter =HasFormula(A1) as the condition
Set the format to make the cell RED & close Conditional Formatting
Test...
Enter =10/5 into Cell A1
See B1 go red
Enter "hello" into Cell A1
See formatting clear.

Good luck - Steve.



"dave m" wrote in message ...
Hey Stevie,
@nd formula in vb works fine on single cell, If i clear the cell line in vb
it dont work at all.
What would be great is if you can detail how to do a columnSame cell Ref) in
many sheets.
Look forward to yr reply.

D.

"dave m" wrote:

Hello all,
I have a complex w/sheet with daily sheets that runs a vlookup based on data
entered. once vlookup cells are populated with values, i copy,paste special
values to force the values into cells at the time of lookup.This prevents
data lookups showing old values on future days. Can i con.format cells so
that if they are vlookups they are 1 colour and another colour if values. I
have tried various formulas with no success and also tried referencing the
data elsewhere with no success. A curly one for frank, bob and max here me
thinks. Look forward to yr assistance as always.

Thanks, David.