-
Solved: Conditional Format VBA not using Range Name Value
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
[vba]
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").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("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
[/vba]
-
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)
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
-
p45cal,
Thanks that did the trick...
JimS
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules