I'm having trouble setting conditional formats with VBA.

I currently have:

With
Worksheets(OPG).Range("D8").FormatConditions.Add(x lCellValue, xlNotBetween,
"=VALUE(LEFT(C8,4))", "=VALUE(RIGHT(C8,4))")
With .Font
.ColorIndex = 3
End With
End With

This gives Subscript out of range on the first line. C8 contains 2.94-3.06
and is a range of accepable values.

Ian
---

Hi Ian,

Try something like:

Sub Tester()
Dim rng As Range
Dim var1 As Variant, var2 As Variant
Dim sh As Worksheet

Set sh = Worksheets(OPG)

Set rng = sh.Range("C8")

var1 = CDbl(Left(rng.Value, 4))
var2 = CDbl(Right(rng.Value, 4))

With rng(1, 2).FormatConditions
.Delete
.Add _
Type:=xlCellValue, _
Operator:=xlBetween, _
Formula1:=var1, _
Formula2:=var2
.Item(1).Font.ColorIndex = 46
End With

End Sub

---
Regards,
Norman


"Ian" wrote in message
...
I'm having trouble setting conditional formats with VBA.

I currently have:

With
Worksheets(OPG).Range("D8").FormatConditions.Add(x lCellValue,
xlNotBetween, "=VALUE(LEFT(C8,4))", "=VALUE(RIGHT(C8,4))")
With .Font
.ColorIndex = 3
End With
End With

This gives Subscript out of range on the first line. C8 contains 2.94-3.06
and is a range of accepable values.

Ian
---

Hi Ian,

Additionaaly, if the worksheet name is OPG, then the line:

Set sh = Worksheets(OPG)

should be:

Set sh = Worksheets("OPG")

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Ian,

Try something like:

Sub Tester()
Dim rng As Range
Dim var1 As Variant, var2 As Variant
Dim sh As Worksheet

Set sh = Worksheets(OPG)

Set rng = sh.Range("C8")

var1 = CDbl(Left(rng.Value, 4))
var2 = CDbl(Right(rng.Value, 4))

With rng(1, 2).FormatConditions
.Delete
.Add _
Type:=xlCellValue, _
Operator:=xlBetween, _
Formula1:=var1, _
Formula2:=var2
.Item(1).Font.ColorIndex = 46
End With

End Sub

---
Regards,
Norman


"Ian" wrote in message
...
I'm having trouble setting conditional formats with VBA.

I currently have:

With
Worksheets(OPG).Range("D8").FormatConditions.Add(x lCellValue,
xlNotBetween, "=VALUE(LEFT(C8,4))", "=VALUE(RIGHT(C8,4))")
With .Font
.ColorIndex = 3
End With
End With

This gives Subscript out of range on the first line. C8 contains
2.94-3.06 and is a range of accepable values.

Ian
---

Hi Norman

I tried your code, but it still fails. I get the same error as before, but
it appears on the "Set sh" line. The sheet name is definitely correct and I
tried it with the original designation (Sheet6), but get a type mismatch
error.

At least your method narrows it down, but I'm still unable to figure out
what's happening.

--
Ian
--
"Norman Jones" wrote in message
...
Hi Ian,

Try something like:

Sub Tester()
Dim rng As Range
Dim var1 As Variant, var2 As Variant
Dim sh As Worksheet

Set sh = Worksheets(OPG)

Set rng = sh.Range("C8")

var1 = CDbl(Left(rng.Value, 4))
var2 = CDbl(Right(rng.Value, 4))

With rng(1, 2).FormatConditions
.Delete
.Add _
Type:=xlCellValue, _
Operator:=xlBetween, _
Formula1:=var1, _
Formula2:=var2
.Item(1).Font.ColorIndex = 46
End With

End Sub

---
Regards,
Norman


"Ian" wrote in message
...
I'm having trouble setting conditional formats with VBA.

I currently have:

With
Worksheets(OPG).Range("D8").FormatConditions.Add(x lCellValue,
xlNotBetween, "=VALUE(LEFT(C8,4))", "=VALUE(RIGHT(C8,4))")
With .Font
.ColorIndex = 3
End With
End With

This gives Subscript out of range on the first line. C8 contains
2.94-3.06 and is a range of accepable values.

Ian
---

Brilliant, thanks Norman. This was alos the problem with my original line.
All working well, now.

--
Ian
--
"Norman Jones" wrote in message
...
Hi Ian,

Additionaaly, if the worksheet name is OPG, then the line:

Set sh = Worksheets(OPG)

should be:

Set sh = Worksheets("OPG")

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Ian,

Try something like:

Sub Tester()
Dim rng As Range
Dim var1 As Variant, var2 As Variant
Dim sh As Worksheet

Set sh = Worksheets(OPG)

Set rng = sh.Range("C8")

var1 = CDbl(Left(rng.Value, 4))
var2 = CDbl(Right(rng.Value, 4))

With rng(1, 2).FormatConditions
.Delete
.Add _
Type:=xlCellValue, _
Operator:=xlBetween, _
Formula1:=var1, _
Formula2:=var2
.Item(1).Font.ColorIndex = 46
End With

End Sub

---
Regards,
Norman


"Ian" wrote in message
...
I'm having trouble setting conditional formats with VBA.

I currently have:

With
Worksheets(OPG).Range("D8").FormatConditions.Add(x lCellValue,
xlNotBetween, "=VALUE(LEFT(C8,4))", "=VALUE(RIGHT(C8,4))")
With .Font
.ColorIndex = 3
End With
End With

This gives Subscript out of range on the first line. C8 contains
2.94-3.06 and is a range of accepable values.

Ian
---