Greetings,

I am trying to change the color of a row (A:X) when different
conditions are met.

1st condition:
A and B are both empty.
The row from A to X gets InteriorIndex = 19
Border lines get also get ColorIndex = 19
2nd condition:
D ="" and H = "Nancy"
The row from A to X gets InteriorIndex = 6
Border lines get also get ColorIndex = xlAutomatic
3rd condition:
D = "" (H = anything else)
The row from A to X gets InteriorIndex = 39
Border lines get also get ColorIndex = xlAutomatic
4th condition:
D "" and E F (if D is not empty there will always be either
an E or an F)
The row from A to X gets InteriorIndex = 37
Border lines get also get ColorIndex = xlAutomatic
5th condition:
D "" and FE
The row from A to X gets InteriorIndex = 38
Border lines get also get ColorIndex = xlAutomatic

I tried to use the Conditional Formatter, but it only does 3
conditions. Could someone show me how this can be done?

TIA

-Minitman

see if this will help

http://www.mrexcel.com/archive/Formatting/12054.html

--


Gary


"Minitman" wrote in message
...
Greetings,

I am trying to change the color of a row (A:X) when different
conditions are met.

1st condition:
A and B are both empty.
The row from A to X gets InteriorIndex = 19
Border lines get also get ColorIndex = 19
2nd condition:
D ="" and H = "Nancy"
The row from A to X gets InteriorIndex = 6
Border lines get also get ColorIndex = xlAutomatic
3rd condition:
D = "" (H = anything else)
The row from A to X gets InteriorIndex = 39
Border lines get also get ColorIndex = xlAutomatic
4th condition:
D "" and E F (if D is not empty there will always be either
an E or an F)
The row from A to X gets InteriorIndex = 37
Border lines get also get ColorIndex = xlAutomatic
5th condition:
D "" and FE
The row from A to X gets InteriorIndex = 38
Border lines get also get ColorIndex = xlAutomatic

I tried to use the Conditional Formatter, but it only does 3
conditions. Could someone show me how this can be done?

TIA

-Minitman

I have written a number of replies in this NG will code on setting mulitple
colors for 3 conditions

"Minitman" wrote:

Greetings,

I am trying to change the color of a row (A:X) when different
conditions are met.

1st condition:
A and B are both empty.
The row from A to X gets InteriorIndex = 19
Border lines get also get ColorIndex = 19
2nd condition:
D ="" and H = "Nancy"
The row from A to X gets InteriorIndex = 6
Border lines get also get ColorIndex = xlAutomatic
3rd condition:
D = "" (H = anything else)
The row from A to X gets InteriorIndex = 39
Border lines get also get ColorIndex = xlAutomatic
4th condition:
D "" and E F (if D is not empty there will always be either
an E or an F)
The row from A to X gets InteriorIndex = 37
Border lines get also get ColorIndex = xlAutomatic
5th condition:
D "" and FE
The row from A to X gets InteriorIndex = 38
Border lines get also get ColorIndex = xlAutomatic

I tried to use the Conditional Formatter, but it only does 3
conditions. Could someone show me how this can be done?

TIA

-Minitman

One of many examples at http://tinyurl.com/b7kly

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Minitman" wrote in message
...
Greetings,

I am trying to change the color of a row (A:X) when different
conditions are met.

1st condition:
A and B are both empty.
The row from A to X gets InteriorIndex = 19
Border lines get also get ColorIndex = 19
2nd condition:
D ="" and H = "Nancy"
The row from A to X gets InteriorIndex = 6
Border lines get also get ColorIndex = xlAutomatic
3rd condition:
D = "" (H = anything else)
The row from A to X gets InteriorIndex = 39
Border lines get also get ColorIndex = xlAutomatic
4th condition:
D "" and E F (if D is not empty there will always be either
an E or an F)
The row from A to X gets InteriorIndex = 37
Border lines get also get ColorIndex = xlAutomatic
5th condition:
D "" and FE
The row from A to X gets InteriorIndex = 38
Border lines get also get ColorIndex = xlAutomatic

I tried to use the Conditional Formatter, but it only does 3
conditions. Could someone show me how this can be done?

TIA

-Minitman

Hey Bob,

Thanks for the reply.

I am not sure how to enter the target (A2:X250 on each of 120 sheets).
Or how to reference the Case's. I am only using Columns A, B, D & H.
I am trying to color the entire row within the target range. I am
really at a loss as to how to make this. I have been paging through
the archives and am left with the questions at the start of this
reply.

Any help would be most appreciated.

TIA

-Minitman


On Mon, 25 Jul 2005 09:51:31 +0100, "Bob Phillips"
wrote:

One of many examples at http://tinyurl.com/b7kly

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Minitman" wrote in message
.. .
Greetings,

I am trying to change the color of a row (A:X) when different
conditions are met.

1st condition:
A and B are both empty.
The row from A to X gets InteriorIndex = 19
Border lines get also get ColorIndex = 19
2nd condition:
D ="" and H = "Nancy"
The row from A to X gets InteriorIndex = 6
Border lines get also get ColorIndex = xlAutomatic
3rd condition:
D = "" (H = anything else)
The row from A to X gets InteriorIndex = 39
Border lines get also get ColorIndex = xlAutomatic
4th condition:
D "" and E F (if D is not empty there will always be either
an E or an F)
The row from A to X gets InteriorIndex = 37
Border lines get also get ColorIndex = xlAutomatic
5th condition:
D "" and FE
The row from A to X gets InteriorIndex = 38
Border lines get also get ColorIndex = xlAutomatic

I tried to use the Conditional Formatter, but it only does 3
conditions. Could someone show me how this can be done?

TIA

-Minitman

This should get you started. It works for every sheet in the workbook

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Sh.Range("A2:X250")) Is Nothing Then
With Target
Select Case .Value
Case 1: .EntireRow.Interior.ColorIndex = 1
Case 2: .EntireRow.Interior.ColorIndex = 2
Case 3: .EntireRow.Interior.ColorIndex = 3
Case 4: .EntireRow.Interior.ColorIndex = 4
Case 5: .EntireRow.Interior.ColorIndex = 5
Case 6: .EntireRow.Interior.ColorIndex = 6
End Select

End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Minitman" wrote in message
...
Hey Bob,

Thanks for the reply.

I am not sure how to enter the target (A2:X250 on each of 120 sheets).
Or how to reference the Case's. I am only using Columns A, B, D & H.
I am trying to color the entire row within the target range. I am
really at a loss as to how to make this. I have been paging through
the archives and am left with the questions at the start of this
reply.

Any help would be most appreciated.

TIA

-Minitman


On Mon, 25 Jul 2005 09:51:31 +0100, "Bob Phillips"
wrote:

One of many examples at http://tinyurl.com/b7kly

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Minitman" wrote in message
.. .
Greetings,

I am trying to change the color of a row (A:X) when different
conditions are met.

1st condition:
A and B are both empty.
The row from A to X gets InteriorIndex = 19
Border lines get also get ColorIndex = 19
2nd condition:
D ="" and H = "Nancy"
The row from A to X gets InteriorIndex = 6
Border lines get also get ColorIndex = xlAutomatic
3rd condition:
D = "" (H = anything else)
The row from A to X gets InteriorIndex = 39
Border lines get also get ColorIndex = xlAutomatic
4th condition:
D "" and E F (if D is not empty there will always be either
an E or an F)
The row from A to X gets InteriorIndex = 37
Border lines get also get ColorIndex = xlAutomatic
5th condition:
D "" and FE
The row from A to X gets InteriorIndex = 38
Border lines get also get ColorIndex = xlAutomatic

I tried to use the Conditional Formatter, but it only does 3
conditions. Could someone show me how this can be done?

TIA

-Minitman

Hey Bob,

Thanks for the sample.

A couple of questions do present them selves.

How are the conditions assigned to the Case's?
And how do I limit the row to only A thru X?

-Minitman


On Mon, 25 Jul 2005 10:38:23 +0100, "Bob Phillips"
wrote:

This should get you started. It works for every sheet in the workbook

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Sh.Range("A2:X250")) Is Nothing Then
With Target
Select Case .Value
Case 1: .EntireRow.Interior.ColorIndex = 1
Case 2: .EntireRow.Interior.ColorIndex = 2
Case 3: .EntireRow.Interior.ColorIndex = 3
Case 4: .EntireRow.Interior.ColorIndex = 4
Case 5: .EntireRow.Interior.ColorIndex = 5
Case 6: .EntireRow.Interior.ColorIndex = 6
End Select

End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

"Minitman" wrote in message
...
Hey Bob,

Thanks for the sample.

A couple of questions do present them selves.

How are the conditions assigned to the Case's?

I have used numbers, Case 1 etc. You can change to text Case "abc" etc.

And how do I limit the row to only A thru X?

It already is, by the intersect.