User Variable passed to Conditional Formatting with VBA

[brainstain]

Somewhat newbie here, Hello,

I made a ledger sheet that includes a daily running balance like a checkbook, and wanted to have the
ability to highlight cells if the value falls below a minimum, BUT if I want to change that conditional minimum value on the fly, I want to be able to enter a number in a separate cell, click a button (which I know how to make a button to run a macro) and have that number passed to a variable that goes into the macro of the Conditional Formatting macro I recorded and allow the minimum balance to change based on user input.

This is a 'part' of the Cond. Format. I recorded with the value of 'less than 50' that I want to be able to change by entering another number in a cell, click a button, and it passes then new value to that variable.

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=50"

So Im guessing the variable would be within the quotes area?
And need to pass the variable some how, (value will only be whole numbers).
I know how to have the macro read the cell of user input, but dont know how to properly assign that value to a proper variable (long, integer...) then pass it to the macro and change it from the original input of 'less than 50'.

Suggestions please?

Thanks

David

 

[mrmmickle1]

You will want to use something like this:

[COLOR=#0000ff]Sub[/COLOR][COLOR=#333333] Test()

[/COLOR][COLOR=#0000ff]     Dim[/COLOR][COLOR=#333333] YourVar [/COLOR][COLOR=#0000ff]As Integer[/COLOR][COLOR=#333333]

[/COLOR][COLOR=#008000]     'The value you want the procedure to take goes in Cell A1[/COLOR][COLOR=#333333]
     YourVar = Range("A1").Value

     Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _[/COLOR]
[COLOR=#333333]     Formula1:="=" & YourVar

[/COLOR][COLOR=#0000ff]End Sub[/COLOR]

Change the variable type for your needs. Link this sub to the button you mentioned.

 

[brainstain]

Thank you very much MrMMickle1, I will try that right now, needed it for a project and will let you know, thanks.

 

[brainstain]

MrMMickle1,

One thing I didnt mention, is that all the cells that I have the Cond. Formula used on,
have formulas, since they are a running balance like a checkbook.
This did seem to work the first time or two, but then when I changed the value in the cell (AW17),
it didnt work as accurate.

Will this work correctly with formulas, or just values in those cells, and
was it meant to be part of the macro for condit. format. or a separate run routine,
and then the user input passed to that condit. format routine?

Below is what Im running from a button, it highlights some, but not all accurate.
Thanks for your valuable input.


Private Sub CommandButton21_Click()
'
' MinBal Macro
'
Dim MinBal As Integer
MinBal = Range("AW17").Value
Range("BB3:BB64").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=" & MinBal
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

brainstain

 

[brainstain]

One thing I just noticed, that its not clearing the previous condition.
I set the user input very high, then very low, and it kept all the conditions from the high value,
so maybe I need to clear the condition in the macro first, and rerun it with this condition?

UPDATE:

I just added the top 2 lines to clear the previous run, and it seems to be working, will keep you posted,
Thanks sir.

Private Sub CommandButton21_Click()
'
' MinBal Macro

'

Range("BB3:BB64").Select
Selection.FormatConditions.Delete
Dim MinBal As Integer
MinBal = Range("AW17").Value
Range("BB3:BB64").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=" & MinBal
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

 

[mrmmickle1]

Your welcome. Let me know if you have any more questions.

 

[brainstain]

I'll send you a link to it when its finished, thanks MrMMickle1.

 

[xx00ludexx]

I'm having an issue getting the code to work the way you suggested to use the variable. I keep seeing a Run Time Error - Invalid procedure call or argument.

Sub Macro3()
'
' Macro3 Macro
'
'Dim test1234 As Double
'test1234 = 0.123
Dim test1234 As Integer
test1234 = 1
'
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLesser, _
Formula1:="=" & test1234
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False


End Sub

Any Suggestions?