Select constants

[sujittalukde]

Can a macro be provided for selecting cells (including hidden) in the active sheet which are :

1. Totally consants ie not used in formula either in same sheet or other sheets

2. Constants ie figures used in formula in the same sheet and/or other sheets

 

[royUK]

For constants in cells

Cells.SpecialCells (xlCellTypeConstants)

 

[sujittalukde]

Thanks royuk,
I am very sorry I forget add one lines with my requirements

Totally consants ie not used in formula either in same sheet or other sheets (Font color Blue)

2. Constants ie figures used in formula in the same sheet and/or other sheets (Font color RED)

 

[sujittalukde]

Some help please!!!!!!!!!1

 

[sujittalukde]

Can this be solved? Help please.

 

[sujittalukde]

Bump!!!!!!!!!!!!!!

 

[Krishnakumar]

Hi,

Define: IsConst

Refers to: =GET.CELL(48,INDIRECT("rc",FALSE))

Select the range and in Conditional formatting..

Condition 1

Formula Is: =AND(NOT(ISTEXT(A1)),LEN(A1)>0,IsConst=FALSE)

Color Blue

Condition 2

Formula Is: =AND(NOT(ISTEXT(A1)),LEN(A1)>0,IsConst)

Color Red

HTH

 

[sujittalukde]

Thanks Kris, but I was thinking for the same to merge with another macro and to make an addin Thats why I need a macro solution

 

[Krishnakumar]

Thanks Kris, but I was thinking for the same to merge with another macro and to make an addin Thats why I need a macro solution

May be..

Sub test()
Dim RngC As Range, RngF As Range, Rng   As Range, r As Range
On Error Resume Next
Set RngC = Selection.SpecialCells(xlCellTypeConstants, 1)
Set RngF = Selection.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0

If Not RngC Is Nothing And Not RngF Is Nothing Then
    Set Rng = Union(RngC, RngF)
    ElseIf Not RngC Is Nothing And RngF Is Nothing Then
        Set Rng = RngC
    ElseIf RngC Is Nothing And Not RngF Is Nothing Then
        Set Rng = RngF
End If

For Each r In Rng
    If r.HasFormula Then
        r.Interior.ColorIndex = 3
    Else
        r.Interior.ColorIndex = 5
    End If
Next
End Sub

 

[sujittalukde]

Thnaks kris for the reply the code has one problem:

It is colouring Formula result as RED Whereas my need was that Those figures which are used for formula should be red. suppose 20 is a static value, but used for formula then color red. If not used for formula anywhere in the same WB say same sheet or other sheet, should be coloured RED.

It is colouring all constants as BLUE. suppose in the above eg., 20 is not used for any formula, then color it as BLUE else if used for formula color RED.

Thus, Formula results will be by default appear in red.