Solved: Conditional Format VBA not using Range Name Value

[JimS]

I want to reference 2 different cells (that have Range Names associated to them) that the user will fill in.
These ranges will be used by the attached VBA to set Conditional Formatting and will also be used as Filter Criteria.

It doesn't like this line of code:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(B1=lValue,NOT(ISBLANK(B1)))"

It actually puts "lValue" in the Conditional Format formula, not the Value in the Range Named "Low" cell.

The hValue works in this line though:
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=hValue

After the user enters the values in the Range Named Cells (hValue and lValue) and runs the Macro the cells on Sheet1 are supposed to be filled in based on their value using Conditional Formatting. Then any cell that was filled in gets copied over to Sheet2.

Not sure how to refernce the Range Named Cells in the "CopyColors" routine either.

Also, not sure if there is a simply way to write this code (see below), open to any ideas.

Thanks...

JimS



Sub FillColors()
Dim hValue As String
Dim lValue As String

Application.ScreenUpdating = False

hValue = Range("High")
lValue = Range("Low")

Sheets("Sheet1").Select
Range("A1").Select

Columns("B:J").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(B1=lValue,NOT(ISBLANK(B1)))"
Selection.FormatConditions(1).Font.ColorIndex = 1
Selection.FormatConditions(1).Interior.ColorIndex = 4
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
, Formula1:=hValue
Selection.FormatConditions(2).Font.ColorIndex = 1
Selection.FormatConditions(2).Interior.ColorIndex = 3
Range("A1").Select

Run ("CopyColors")
End Sub

Sub CopyColors()
Rows("1:1").Select
Selection.Insert Shift:=xlDown

Range("B1:J1").Select
Selection.AutoFilter

Selection.AutoFilter Field:=1, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("B:B").Select
Selection.Copy
Sheets("Sheet2").Select
Columns("B:B").Select
ActiveSheet.Paste

Sheets("Sheet1").Select
Selection.AutoFilter Field:=1
Selection.AutoFilter Field:=2, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("C:C").Select
ActiveSheet.Paste

Sheets("Sheet1").Select
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=3, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("D:D").Select
ActiveSheet.Paste

Sheets("Sheet1").Select
Selection.AutoFilter Field:=3
Selection.AutoFilter Field:=4, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("E:E").Select
ActiveSheet.Paste

Sheets("Sheet1").Select
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=5, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("F:F").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("F:F").Select
ActiveSheet.Paste

Sheets("Sheet1").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("G:G").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("G:G").Select
ActiveSheet.Paste

Sheets("Sheet1").Select
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=7, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("H:H").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("H:H").Select
ActiveSheet.Paste

Sheets("Sheet1").Select
Selection.AutoFilter Field:=7
Selection.AutoFilter Field:=8, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("I:I").Select
ActiveSheet.Paste

Sheets("Sheet1").Select
Selection.AutoFilter Field:=8
Selection.AutoFilter Field:=9, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("J:J").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("J:J").Select
ActiveSheet.Paste

Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select

Sheets("Sheet1").Select
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp

Range("A1").Select

Application.ScreenUpdating = True

End Sub

[p45cal]

try replacing:
"=AND(B1=lValue,NOT(ISBLANK(B1)))"
with:
"=AND(B1=" & """" & lValue & """" & ",NOT(ISBLANK(B1)))"
The above should work.

However you'll still be left with the problem of not retaining the colours of conditional formatting. Use vba to colour the cells directly after you've pasted them without using conditional formatting. I suspect that the pasted cells will be selected then (?) so add a line such as
selection.interior.colorindex = 3
(not tried nor tested)

[JimS]

p45cal,

Thanks that did the trick...

JimS