Conditional formatting

[Ian]

I'm having trouble setting conditional formats with VBA.

I currently have:

With
Worksheets(OPG).Range("D8").FormatConditions.Add(xlCellValue, 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
---

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> I'm having trouble setting conditional formats with VBA.
>
> I currently have:
>
> With
> Worksheets(OPG).Range("D8").FormatConditions.Add(xlCellValue,
> 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
> ---
>
>

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> I'm having trouble setting conditional formats with VBA.
>>
>> I currently have:
>>
>> With
>> Worksheets(OPG).Range("D8").FormatConditions.Add(xlCellValue,
>> 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
>> ---
>>
>>
>
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> I'm having trouble setting conditional formats with VBA.
>>
>> I currently have:
>>
>> With
>> Worksheets(OPG).Range("D8").FormatConditions.Add(xlCellValue,
>> 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
>> ---
>>
>>
>
>

[Ian]

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

--
Ian
--
"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> I'm having trouble setting conditional formats with VBA.
>>>
>>> I currently have:
>>>
>>> With
>>> Worksheets(OPG).Range("D8").FormatConditions.Add(xlCellValue,
>>> 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
>>> ---
>>>
>>>
>>
>>
>
>