Formula or not?

  • Thread starter Thread starter J. Avelar
  • Start date Start date
J

J. Avelar

Hi,

How can one determine if a cells contais a value or a formula? I'm trying to
use Conditional Formatting to color code the cells based on their content
(formula or value).

Does anyone know?

Thanks
 
Here's a nifty macro that some kind folks in the group gave me some time
ago........it works super.
(watch out for email word-wrap)

Vaya con Dios,
Chuck, CABGx3


Public Sub IDFormulae()
Dim response As Variant
response = Application.InputBox("Identify Cells containing formulas with:" &
_
vbNewLine & "1 - Red Border" & vbTab & _
vbTab & "5 - Blue Background" & _
vbNewLine & "2 - Blue Border" & vbTab & _
vbTab & "6 - Green Background" & _
vbNewLine & "3 - Green Border" & vbTab & _
vbTab & "7 - Clear Border Color" & _
vbNewLine & "4 - Yellow Background" & _
vbTab & "8 - Clear Background Color", _
Title:="Format Formulas as follows:", Type:=1)

If response = False Then Exit Sub
On Error Resume Next
With ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
Select Case response
Case 1
.Borders.ColorIndex = 3 'sets Borders Red
.Borders.Weight = xlThick 'makes Borders thick
Case 2
.Borders.ColorIndex = 5 'sets Borders Blue
.Borders.Weight = xlThick 'makes Borders thick
Case 3
.Borders.ColorIndex = 4 'sets Borders green
.Borders.Weight = xlThick 'makes Borders thick
Case 4
.Interior.ColorIndex = 36 'sets background yellow
Case 5
.Interior.ColorIndex = 34 'sets Background lite blue
Case 6
.Interior.ColorIndex = 35 'sets Background lite green
Case 7
.Borders.ColorIndex = xlColorIndexNone 'clears Borders set by
above
Case 8
.Interior.ColorIndex = xlColorIndexNone 'clears Backgrounds set
by above
Case Else
MsgBox "Not a valid option"
' Note that clearing Borders or Backgrounds in Cases 7 & 8
' only clears those which were set by this macro and does not
' affect other borders or backgrounds elsewhere on the sheet
End Select
End With
On Error GoTo 0
End Sub
 
Thanks, Chuck. By your answer I understand that it cannot be done without
VBA, right?


CLR said:
Here's a nifty macro that some kind folks in the group gave me some time
ago........it works super.
(watch out for email word-wrap)

Vaya con Dios,
Chuck, CABGx3


Public Sub IDFormulae()
Dim response As Variant
response = Application.InputBox("Identify Cells containing formulas with:" &
_
vbNewLine & "1 - Red Border" & vbTab & _
vbTab & "5 - Blue Background" & _
vbNewLine & "2 - Blue Border" & vbTab & _
vbTab & "6 - Green Background" & _
vbNewLine & "3 - Green Border" & vbTab & _
vbTab & "7 - Clear Border Color" & _
vbNewLine & "4 - Yellow Background" & _
vbTab & "8 - Clear Background Color", _
Title:="Format Formulas as follows:", Type:=1)

If response = False Then Exit Sub
On Error Resume Next
With ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
Select Case response
Case 1
.Borders.ColorIndex = 3 'sets Borders Red
.Borders.Weight = xlThick 'makes Borders thick
Case 2
.Borders.ColorIndex = 5 'sets Borders Blue
.Borders.Weight = xlThick 'makes Borders thick
Case 3
.Borders.ColorIndex = 4 'sets Borders green
.Borders.Weight = xlThick 'makes Borders thick
Case 4
.Interior.ColorIndex = 36 'sets background yellow
Case 5
.Interior.ColorIndex = 34 'sets Background lite blue
Case 6
.Interior.ColorIndex = 35 'sets Background lite green
Case 7
.Borders.ColorIndex = xlColorIndexNone 'clears Borders set by
above
Case 8
.Interior.ColorIndex = xlColorIndexNone 'clears Backgrounds set
by above
Case Else
MsgBox "Not a valid option"
' Note that clearing Borders or Backgrounds in Cases 7 & 8
' only clears those which were set by this macro and does not
' affect other borders or backgrounds elsewhere on the sheet
End Select
End With
On Error GoTo 0
End Sub






J. Avelar said:
Hi,

How can one determine if a cells contais a value or a formula? I'm
Click to expand...
trying
Click to expand...
 
I dunno, never really had occasion to think about it. This macro works so
fine, I just keep it in my Personal.xls file and use it when I need it, or
if I just want to see where the formulas are.......do F5 > Special >
Formulas and that will highlight all the formulas in the sheet........

Vaya con Dios,
Chuck, CABGx3



J. Avelar said:
Thanks, Chuck. By your answer I understand that it cannot be done without
VBA, right?


CLR said:
Here's a nifty macro that some kind folks in the group gave me some time
ago........it works super.
(watch out for email word-wrap)

Vaya con Dios,
Chuck, CABGx3


Public Sub IDFormulae()
Dim response As Variant
response = Application.InputBox("Identify Cells containing formulas
Click to expand...
with:"
Click to expand...
 
AFAIK a formula can not detect a formula. VBA it is.

Formulas and conditional formatting recalculate every time. You may not need
that. If you need to color formulas, maybe on the separate cell entry or at
the click of a button is often enough ?

Also; menu Edit > Goto > Special, check Formulas, OK. Now click an ugly
color in the Format toolbar and they're colored.

HTH. Best wishes Harald

J. Avelar said:
Thanks, Chuck. By your answer I understand that it cannot be done without
VBA, right?
Click to expand...
 
Hi,

Try this:

1) go to menu Insert>Name>Define...
2) write a name - say HasFormula
3) input the following formula:
=GET.CELL(48,INDIRECT("RC",0))+0*now()
4) press Add, OK
5) select the range to apply conditional formatting to
6) go to menu Format>Conditional Formatting...
7) choose Formula is... in the first dropdown box
8) write the following formula: =HasFormula

Note: although I have personally never had issues with XML functions, some
people report that they may occasionally cause Excel shutdown and loss of
unsaved data. So you use it at your own risk.

Also, Jan-Karel Pieterse has a very neat file full of named XML formulas
examples here: http://www.jkp-ads.com/Articles/ExcelNames.htm

Regards,
KL
 
Wow! What a surprise! I never had a version higher than 2K and it works
great on my XL97 and XL2K. If you use a non-English version of the office
then that is a different issue as both the functions and the "RC" may need
to be translated to your system's language.

Regards,
KL
 
Very good response. I bet it's the international issues.
Wow! What a surprise! I never had a version higher than 2K and it works
great on my XL97 and XL2K. If you use a non-English version of the office
then that is a different issue as both the functions and the "RC" may need
to be translated to your system's language.

Regards,
KL
Click to expand...
<<snipped>>
 
Hi,

I am not sure, but if you are using a Portuguese version of Office you may
want to try:

=OBTER.CEL(48;INDIRECTO("LC";0))+0*HOJE()

Regards,
KL
 
Back
Top