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.

You could add a function that tests the contents & changes the cell colour...

Open VB, then open Sheet 1

Paste the following in...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.HasFormula Then
If Target.Value2 Like "=*" Then
Target.Interior.Color = RGB(255, 50, 50)
Else
Target.Interior.Color = RGB(255, 255, 255)
End If
End If
End Sub

Now when you type in A1, colour changes red if it is a function & green if not!



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

You could write a UDF to test if a cell has a formula

Function IsFormula(rng As Range)
IsFormula = rng.HasFormula
End Function

and then use this UDF in the CF, like

=IsFormula(A1) for one colour

and

=A1<"" for the next

--
HTH

Bob Phillips

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

DOH!

Heres the real 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

The other one was complete nonsense (last minute paste!)


1 note: If you want all cells to do this then remove "If Target.Address = "$A$1" Then"

If you want a section of cells to do this (E.G. a range from A1 ~ B10) then let me know & I'll show you that one.





"Stevie_mac" wrote in message ...
You could add a function that tests the contents & changes the cell colour...

Open VB, then open Sheet 1

Paste the following in...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.HasFormula Then
If Target.Value2 Like "=*" Then
Target.Interior.Color = RGB(255, 50, 50)
Else
Target.Interior.Color = RGB(255, 255, 255)
End If
End If
End Sub

Now when you type in A1, colour changes red if it is a function & green if not!



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

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.

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.

Hey bob, I just realised, I suggested pretty much the same thing you did (see my 2nd reply)

Thing is, I looked at your solution yesterday but didn't actually make any effort to comprehend it. I simply looked at
it, thought that looks clever, don't have enough space in my brain to process it.

As it turns out, your solution was / is much better than my 1st suggestion (it is the correct way IMHO)

Why I didn't comprehend it (and quite possibly why Dave didn't use it) was I believe, because of the acronyms you used.

I now understand (since I came up with the same solution) that UDF = User Defined Function & CF = Conditional
Formatting.

I gotta say, there are times & places for acronyms (I use them myself - USB, TCP/IP) but these just threw me!

In my 2nd solution I think I used 1 acronym (VB) & but I'm pretty sure the guy will understand what I'm telling him to
do!

Anyway, for what its worth, just though I'd share!

TTFN - Steve.

(Ta Ta for now)

"Bob Phillips" wrote in message ...
You could write a UDF to test if a cell has a formula

Function IsFormula(rng As Range)
IsFormula = rng.HasFormula
End Function

and then use this UDF in the CF, like

=IsFormula(A1) for one colour

and

=A1<"" for the next

--
HTH

Bob Phillips

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

Thanks Steve,

I must admit I might use acronyms too liberally :-). The vice of the lazy I
guess.

I thought everyone knew UDF :-), but I know I shouldn't assume. As to CF, my
only(poor) defence is that the OP (original poster :-)) used the term
Conditional Format, so I thought the context gave it.

I will strive to be clearer, after all that is why we bother in the first
place <vbg

Thanks Again

Bob


"Stevie_mac" wrote in message
...
Hey bob, I just realised, I suggested pretty much the same thing you did
(see my 2nd reply)

Thing is, I looked at your solution yesterday but didn't actually make any
effort to comprehend it. I simply looked at
it, thought that looks clever, don't have enough space in my brain to
process it.

As it turns out, your solution was / is much better than my 1st suggestion
(it is the correct way IMHO)

Why I didn't comprehend it (and quite possibly why Dave didn't use it) was
I believe, because of the acronyms you used.

I now understand (since I came up with the same solution) that UDF = User
Defined Function & CF = Conditional
Formatting.

I gotta say, there are times & places for acronyms (I use them myself -
USB, TCP/IP) but these just threw me!

In my 2nd solution I think I used 1 acronym (VB) & but I'm pretty sure the
guy will understand what I'm telling him to
do!

Anyway, for what its worth, just though I'd share!

TTFN - Steve.

(Ta Ta for now)

"Bob Phillips" wrote in message
...
You could write a UDF to test if a cell has a formula

Function IsFormula(rng As Range)
IsFormula = rng.HasFormula
End Function

and then use this UDF in the CF, like

=IsFormula(A1) for one colour

and

=A1<"" for the next

--
HTH

Bob Phillips

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

<G

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

PS I know TTFN, it was someone's catch-phrase as I recall, eludes me as to
whom.

Bob

"Bob Phillips" wrote in message
...

"Stevie_mac" wrote in message
...
Anyway, for what its worth, just though I'd share!

TTFN - Steve.

(Ta Ta for now)

Tigger!

"Bob Phillips" wrote in message ...
PS I know TTFN, it was someone's catch-phrase as I recall, eludes me as to
whom.

Bob

"Bob Phillips" wrote in message
...

"Stevie_mac" wrote in message
...
Anyway, for what its worth, just though I'd share!

TTFN - Steve.

(Ta Ta for now)

Of course, how stupid of me to forget!

"Stevie_mac" wrote in message
...
Tigger!

"Bob Phillips" wrote in message
...
PS I know TTFN, it was someone's catch-phrase as I recall, eludes me as
to
whom.

Bob

"Bob Phillips" wrote in message
...

"Stevie_mac" wrote in message
...
Anyway, for what its worth, just though I'd share!

TTFN - Steve.

(Ta Ta for now)

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.

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.