Conditional formatting code wont work
 

Hi I am trying to code my conditional formatting as I have more than 3
clauses to use.

I cant seem to get the code to work no matter what I name the sub. What do
I need to name it?

Select Case ActiveCell.Value
Case namedrange1 And < namedrange2
Selection.Interior.ColorIndex = 6
Case namedrange2 And <namedrange 3
Selection.Interior.ColorIndex = 5
End Select

If I need to write some more code what would it include?

Thanks for your help.
Rob

Conditional formatting code wont work
 

Are you using Conditional Formatting (i.e. Format Conditional
Formatting ... from the menu) or are you trying to code it into a sub?

Conditional formatting code wont work
 

I am trying to code it.

I have 4 clauses and want to use a sheet with cells as named ranges to avoid
the menus in conditional formatting

Thanks for any suggestions you might have.
Rob

"Nick Hebb" wrote in message
oups.com...
Are you using Conditional Formatting (i.e. Format Conditional
Formatting ... from the menu) or are you trying to code it into a sub?

Conditional formatting code wont work
 

Have you tried namedrange1.value, namedrange2.value, etc?

Conditional formatting code wont work
 

I recently answered a similar question with a full code example. try
searching this NG

"Robert Hargreaves" wrote in message
...
Hi I am trying to code my conditional formatting as I have more than 3
clauses to use.

I cant seem to get the code to work no matter what I name the sub. What
do I need to name it?

Select Case ActiveCell.Value
Case namedrange1 And < namedrange2
Selection.Interior.ColorIndex = 6
Case namedrange2 And <namedrange 3
Selection.Interior.ColorIndex = 5
End Select

If I need to write some more code what would it include?

Thanks for your help.
Rob

Conditional formatting code wont work
 

You could try something like the following:

Public Sub ConditionalFill()

Dim val As Long
Dim nr1 As Long
Dim nr2 As Long
Dim nr3 As Long

val = ActiveCell.Value
nr1 = Range(ActiveWorkbook.Names("namedrange1")).Value
nr2 = Range(ActiveWorkbook.Names("namedrange2")).Value
nr3 = Range(ActiveWorkbook.Names("namedrange3")).Value

Select Case True
Case val nr1 And val < nr2
ActiveCell.Interior.ColorIndex = 6
Case val nr2 And val < nr3
ActiveCell.Interior.ColorIndex = 5
Case Else
ActiveCell.Interior.ColorIndex = xlColorIndexNone
End Select

'If val nr1 And val < nr2 Then
' ActiveCell.Interior.ColorIndex = 6
'ElseIf val nr2 And val < nr3 Then
' ActiveCell.Interior.ColorIndex = 5
'Else
' ActiveCell.Interior.ColorIndex = xlColorIndexNone
'End If

End Sub


Personally, I would use the If..ElseIf structure (commented out)
instead of the Select Case. First, it's more intuitive to code, and
second it's reportly faster.

Also, the above code assumes Long for the cell value type - change that
as need be.