VBA problem with conditional formating using .address to point to cell with conditional value

[Jonstrup]

Hi all

Can someone help me with a piece of conditional format via VBA. It is doing my head in.

Excel somehow changes the input value from my input box - it is simply different to what I select when prompted.

I want to set a conditional format. First I prompt user to select which cell to format, then I prompt to select a cell that contains the value from which the condition is evaluated.

For instance I select G69 is the Cell to be formatted and then A69 as the cell with the value (the value in A69 might change due to other inputs which is why I am doing all this).

Problem is that the conditional format does not look in A69 for the conditional value. I makes it a circular reference to G69. Initially I figured it couldn't work with an .address - but it seems it points at the address. It's just the wrong one as it points to the cell I want to format instead of the cell with conditional value.

Can anyone see what I am doing wrong.

Sub SelectedRangeStandardValue()


'ActiveSheet.Unprotect Password:="test"


  Dim ActSheet As Worksheet
    Dim SelRange As Range
    Dim Response As Range
    
    Set ActSheet = ActiveSheet
    Set SelRange = Selection
    Set Response = Selection
    
Application.DisplayAlerts = False
Set SelRange = Application.InputBox(Prompt:= _
                "Choose area", _
                    Title:="Click area", Type:=8)


          ' Run the Input Box.
      Response = Application.InputBox(Prompt:= _
                "Choose standard for format", _
                    Title:="Click area", Type:=8)


      ' Check to see if Cancel was pressed.
  If Response <> False Then
      End If
     
'clear any lingering formats - just to be sure


     SelRange.ClearFormats

'just some recorded macro altered to fit with my inputbox. I have done a similar solution with an integer which works. The response.address points to an address. It is just the wrong one-
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:=Response.Address
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.599963377788629
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:=Response.Address
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 9223420
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:=Response.Address
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.799981688894314
    End With
    Selection.FormatConditions(1).StopIfTrue = False


'ActiveSheet.Protect Password:="test"


End Sub

 

[Norie]

Shouldn't you be using Value to get the value from the cell?

By the way, why are you using Selection as the range for the formatting instead of the range, SelRange, the user selected earlier?

 

[Jonstrup]

Hi Noire

Thank you for your reply.

Well, you might be right but when I look at how conditional formating works when you do them manually they can refer to the cell address rather than a static value. As I want to be able to adjust the value be changing the cell value I want to refer to the cell by address. I might be wrong.

I kept getting error messages when I tried different ways to apply the format. This way is based on a recorded macro I have adjusted to my needs. Only way I could get to work was this way. And it works as intended in several different scenarios - and never change a winning team right=)

Any idea as to why the cell address in Response - $A69 - changes to SelRange "$G69" when the sub ends?

 

[Norie]

You need an equals sign before the address.

Sub SelectedRangeStandardValue()
Dim ActSheet As Worksheet
Dim SelRange As Range
Dim Response As Range

    'ActiveSheet.Unprotect Password:="test"

    Application.DisplayAlerts = False
    
    Set SelRange = Application.InputBox(Prompt:= _
                                        "Choose area", _
                                        Title:="Click area", Type:=8)


    ' Run the Input Box.
    Set Response = Application.InputBox(Prompt:= _
                                    "Choose standard for format", _
                                    Title:="Click area", Type:=8)

    'clear any lingering formats - just to be sure

    With SelRange
        .ClearFormats

    'just some recorded macro altered to fit with my inputbox. I have done a similar solution with an integer which works. The response.address points to an address. It is just the wrong one-
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
                              Formula1:="=" & Response.Address
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent3
            .TintAndShade = 0.599963377788629
        End With
        .FormatConditions(1).StopIfTrue = False
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
                              Formula1:="=" & Response.Address
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 9223420
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
                              Formula1:="=" & Response.Address
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.799981688894314
        End With
        .FormatConditions(1).StopIfTrue = False

    End With

    'ActiveSheet.Protect Password:="test"

End Sub

 

[Jonstrup]

woop woop. that seems to work.

Actually, when I first saw your code I thought you made a typo because you hadn't put " at the end. But it turns out that is how you do it. So I am going to bed smarter. Not bad I think.

Thank you.