ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Conditional Format (https://www.excelbanter.com/excel-worksheet-functions/218012-conditional-format.html)

LiAD
Conditional Format
 
Hi,

Is there any way to extend a conditional format to greater than three
conditions? I need five.

Thanks

Mike H
Conditional Format
 
Hi,

One way is to tilise the worksheet change event and select case.

Right click your sheet tab, view code and paste this in which currently
works on A1 change the vauluse and colours to suit

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("A1").Value
Case Is = 1
icolor = 6
Case Is = 2
icolor = 9
Case Is = 3
icolor = 12
Case Is = 5
icolor = 15
Case Is = 6
icolor = 22
Case Else
icolor = xlNone
End Select
Range("A1").Interior.ColorIndex = icolor
End Sub

Mike

"LiAD" wrote:

Hi,

Is there any way to extend a conditional format to greater than three
conditions? I need five.

Thanks

David Biddulph[_2_]
Conditional Format
 
Excel 2007, or VBA.
--
David Biddulph

"LiAD" wrote in message
...
Hi,

Is there any way to extend a conditional format to greater than three
conditions? I need five.

Thanks

LiAD
Conditional Format
 
Thanks,

Works fine for A1 but how do i extend the range? Sorry I know zero on VB.

Thanks

"Mike H" wrote:

Hi,

One way is to tilise the worksheet change event and select case.

Right click your sheet tab, view code and paste this in which currently
works on A1 change the vauluse and colours to suit

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("A1").Value
Case Is = 1
icolor = 6
Case Is = 2
icolor = 9
Case Is = 3
icolor = 12
Case Is = 5
icolor = 15
Case Is = 6
icolor = 22
Case Else
icolor = xlNone
End Select
Range("A1").Interior.ColorIndex = icolor
End Sub

Mike

"LiAD" wrote:

Hi,

Is there any way to extend a conditional format to greater than three
conditions? I need five.

Thanks

LiAD
Conditional Format
 
Sorry should have said its 2003 and I need the range to go horizontal not
vertical.

Thanks

"LiAD" wrote:

Thanks,

Works fine for A1 but how do i extend the range? Sorry I know zero on VB.

Thanks

"Mike H" wrote:

Hi,

One way is to tilise the worksheet change event and select case.

Right click your sheet tab, view code and paste this in which currently
works on A1 change the vauluse and colours to suit

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("A1").Value
Case Is = 1
icolor = 6
Case Is = 2
icolor = 9
Case Is = 3
icolor = 12
Case Is = 5
icolor = 15
Case Is = 6
icolor = 22
Case Else
icolor = xlNone
End Select
Range("A1").Interior.ColorIndex = icolor
End Sub

Mike

"LiAD" wrote:

Hi,

Is there any way to extend a conditional format to greater than three
conditions? I need five.

Thanks

LiAD
Conditional Format
 
Using 2003.

Solution wise I dont mind if it has to be VBA, just means I won't understand
a sausage. I can sort of understand the programs that others write but can't
even get close to doing one myself.

Thanks

"David Biddulph" wro

Excel 2007, or VBA.
--
David Biddulph

"LiAD" wrote in message
...
Hi,

Is there any way to extend a conditional format to greater than three
conditions? I need five.

Thanks

Mike H
Conditional Format
 
Hi,

This now works for row 1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Rows(1)) Is Nothing Then
Select Case Target.Value
Case Is = 1
icolor = 6
Case Is = 2
icolor = 9
Case Is = 3
icolor = 12
Case Is = 4
icolor = 15
Case Is = 5
icolor = 22
Case Else
icolor = xlNone
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub

"LiAD" wrote:

Sorry should have said its 2003 and I need the range to go horizontal not
vertical.

Thanks

"LiAD" wrote:

Thanks,

Works fine for A1 but how do i extend the range? Sorry I know zero on VB.

Thanks

"Mike H" wrote:

Hi,

One way is to tilise the worksheet change event and select case.

Right click your sheet tab, view code and paste this in which currently
works on A1 change the vauluse and colours to suit

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("A1").Value
Case Is = 1
icolor = 6
Case Is = 2
icolor = 9
Case Is = 3
icolor = 12
Case Is = 5
icolor = 15
Case Is = 6
icolor = 22
Case Else
icolor = xlNone
End Select
Range("A1").Interior.ColorIndex = icolor
End Sub

Mike

"LiAD" wrote:

Hi,

Is there any way to extend a conditional format to greater than three
conditions? I need five.

Thanks

LiAD
Conditional Format
 
Seems to work perfectly

thankyou

"Mike H" wrote:

Hi,

This now works for row 1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Rows(1)) Is Nothing Then
Select Case Target.Value
Case Is = 1
icolor = 6
Case Is = 2
icolor = 9
Case Is = 3
icolor = 12
Case Is = 4
icolor = 15
Case Is = 5
icolor = 22
Case Else
icolor = xlNone
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub

"LiAD" wrote:

Sorry should have said its 2003 and I need the range to go horizontal not
vertical.

Thanks

"LiAD" wrote:

Thanks,

Works fine for A1 but how do i extend the range? Sorry I know zero on VB.

Thanks

"Mike H" wrote:

Hi,

One way is to tilise the worksheet change event and select case.

Right click your sheet tab, view code and paste this in which currently
works on A1 change the vauluse and colours to suit

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("A1").Value
Case Is = 1
icolor = 6
Case Is = 2
icolor = 9
Case Is = 3
icolor = 12
Case Is = 5
icolor = 15
Case Is = 6
icolor = 22
Case Else
icolor = xlNone
End Select
Range("A1").Interior.ColorIndex = icolor
End Sub

Mike

"LiAD" wrote:

Hi,

Is there any way to extend a conditional format to greater than three
conditions? I need five.

Thanks

Mike H
Conditional Format
 
glad i could help

"LiAD" wrote:

Seems to work perfectly

thankyou

"Mike H" wrote:

Hi,

This now works for row 1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Rows(1)) Is Nothing Then
Select Case Target.Value
Case Is = 1
icolor = 6
Case Is = 2
icolor = 9
Case Is = 3
icolor = 12
Case Is = 4
icolor = 15
Case Is = 5
icolor = 22
Case Else
icolor = xlNone
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub

"LiAD" wrote:

Sorry should have said its 2003 and I need the range to go horizontal not
vertical.

Thanks

"LiAD" wrote:

Thanks,

Works fine for A1 but how do i extend the range? Sorry I know zero on VB.

Thanks

"Mike H" wrote:

Hi,

One way is to tilise the worksheet change event and select case.

Right click your sheet tab, view code and paste this in which currently
works on A1 change the vauluse and colours to suit

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("A1").Value
Case Is = 1
icolor = 6
Case Is = 2
icolor = 9
Case Is = 3
icolor = 12
Case Is = 5
icolor = 15
Case Is = 6
icolor = 22
Case Else
icolor = xlNone
End Select
Range("A1").Interior.ColorIndex = icolor
End Sub

Mike

"LiAD" wrote:

Hi,

Is there any way to extend a conditional format to greater than three
conditions? I need five.

Thanks


All times are GMT +1. The time now is 08:33 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com