You are not taking Steve's advice and using Conditional Formatting.

I don't understand why you are getting the first problem. but the second
would be addressed by conditional formatting. Otherwise, you just run the
essence of that code on all of the cells in the usedrange.

But conditional formatting is better.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"dave m" wrote in message
...
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.