Adding colours as a function
 

hi

Is it possible to highlight a row when a particular cell reaches zero?? For
example if my stock level of oranges in col G reaches zero after orders are
placed then can i run something that will automatically change that row to
red?

Thanks

Adding colours as a function
 

Conditional formatting

http://office.microsoft.com/en-us/as...116611033.aspx

"Billjary" wrote in message
...
hi

Is it possible to highlight a row when a particular cell reaches zero??
For
example if my stock level of oranges in col G reaches zero after orders
are
placed then can i run something that will automatically change that row to
red?

Thanks

Adding colours as a function
 

Look in HELP for Conditional Formatting

--
Kind regards,

Niek Otten

"Billjary" wrote in message ...
hi

Is it possible to highlight a row when a particular cell reaches zero?? For
example if my stock level of oranges in col G reaches zero after orders are
placed then can i run something that will automatically change that row to
red?

Thanks

Adding colours as a function
 

You can use "Conditional Formating" under the "Format" menu. One of the
options is to check the value of the current cell and format it in a certain
way if the condition is fulfilled.

"Billjary" wrote:

hi

Is it possible to highlight a row when a particular cell reaches zero?? For
example if my stock level of oranges in col G reaches zero after orders are
placed then can i run something that will automatically change that row to
red?

Thanks

Adding colours as a function
 

If you want to change the entire row to red, you will need VBA. Put the
macro below in the sheet module for your sheet. To access that module,
right-click on the sheet tab, select View Code, and paste this macro into
that module. Click on the "X" in the top right corner to get back to your
sheet.
This macro will color the entire row red if the corresponding cell in
Column G goes to zero or less. It will remove the red color if the value
goes above zero. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
If Target.Value <= 0 Then
Target.EntireRow.Interior.ColorIndex = 3
Else
Target.EntireRow.Interior.ColorIndex = xlNone
End If
End If
End Sub

"Billjary" wrote in message
...
hi

Is it possible to highlight a row when a particular cell reaches zero??
For
example if my stock level of oranges in col G reaches zero after orders
are
placed then can i run something that will automatically change that row to
red?

Thanks

Adding colours as a function
 

Otto

Thanks very much. This idea is exactly what i need. I do have one problem
tho. I've added exactly as you have entered it below following your
instructions. I am getting a compile error :

Ambiguous name detected: Worksheet_Change

Can you help??

Thanks again

"Otto Moehrbach" wrote:

If you want to change the entire row to red, you will need VBA. Put the
macro below in the sheet module for your sheet. To access that module,
right-click on the sheet tab, select View Code, and paste this macro into
that module. Click on the "X" in the top right corner to get back to your
sheet.
This macro will color the entire row red if the corresponding cell in
Column G goes to zero or less. It will remove the red color if the value
goes above zero. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
If Target.Value <= 0 Then
Target.EntireRow.Interior.ColorIndex = 3
Else
Target.EntireRow.Interior.ColorIndex = xlNone
End If
End If
End Sub

"Billjary" wrote in message
...
hi

Is it possible to highlight a row when a particular cell reaches zero??
For
example if my stock level of oranges in col G reaches zero after orders
are
placed then can i run something that will automatically change that row to
red?

Thanks

Adding colours as a function
 

Otto

"you the man" !!!!!!!!!!!!

I took out one of the private sub lines and all works fine!!


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
If Target.Value <= 0 Then
Target.EntireRow.Interior.ColorIndex = 3
Else
Target.EntireRow.Interior.ColorIndex = xlNone
End If
End If
End Sub



Thanks ever so much

"Billjary" wrote:

Otto

Thanks very much. This idea is exactly what i need. I do have one problem
tho. I've added exactly as you have entered it below following your
instructions. I am getting a compile error :

Ambiguous name detected: Worksheet_Change

Can you help??

Thanks again

"Otto Moehrbach" wrote:

If you want to change the entire row to red, you will need VBA. Put the
macro below in the sheet module for your sheet. To access that module,
right-click on the sheet tab, select View Code, and paste this macro into
that module. Click on the "X" in the top right corner to get back to your
sheet.
This macro will color the entire row red if the corresponding cell in
Column G goes to zero or less. It will remove the red color if the value
goes above zero. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
If Target.Value <= 0 Then
Target.EntireRow.Interior.ColorIndex = 3
Else
Target.EntireRow.Interior.ColorIndex = xlNone
End If
End If
End Sub

"Billjary" wrote in message
...
hi

Is it possible to highlight a row when a particular cell reaches zero??
For
example if my stock level of oranges in col G reaches zero after orders
are
placed then can i run something that will automatically change that row to
red?

Thanks

Adding colours as a function
 

You have it right. I don't know how I managed to put in both lines. Otto
"Billjary" wrote in message
...
Otto

"you the man" !!!!!!!!!!!!

I took out one of the private sub lines and all works fine!!


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
If Target.Value <= 0 Then
Target.EntireRow.Interior.ColorIndex = 3
Else
Target.EntireRow.Interior.ColorIndex = xlNone
End If
End If
End Sub



Thanks ever so much

"Billjary" wrote:

Otto

Thanks very much. This idea is exactly what i need. I do have one problem
tho. I've added exactly as you have entered it below following your
instructions. I am getting a compile error :

Ambiguous name detected: Worksheet_Change

Can you help??

Thanks again

"Otto Moehrbach" wrote:

If you want to change the entire row to red, you will need VBA. Put
the
macro below in the sheet module for your sheet. To access that module,
right-click on the sheet tab, select View Code, and paste this macro
into
that module. Click on the "X" in the top right corner to get back to
your
sheet.
This macro will color the entire row red if the corresponding cell
in
Column G goes to zero or less. It will remove the red color if the
value
goes above zero. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
If Target.Value <= 0 Then
Target.EntireRow.Interior.ColorIndex = 3
Else
Target.EntireRow.Interior.ColorIndex = xlNone
End If
End If
End Sub

"Billjary" wrote in message
...
hi

Is it possible to highlight a row when a particular cell reaches
zero??
For
example if my stock level of oranges in col G reaches zero after
orders
are
placed then can i run something that will automatically change that
row to
red?

Thanks

Adding colours as a function
 

I am trying to change the color of a row based on 1 cell with 7 different
inputs giving 7 diff. colors. (Using data validation to prevent any other
input)

I adapted the code posted here (Thank you Otto) And have it working fine,
except;
If I select several lines and change them all at once (Ctrl+Enter) I get an
error message;

Run-time error '13':
Type mismatch

Is there a more stable method?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Target.Value = 1 Then
Target.EntireRow.Interior.ColorIndex = 6
End If
If Target.Value = 2 Then
Target.EntireRow.Interior.ColorIndex = 4
End If
If Target.Value = 3 Then
Target.EntireRow.Interior.ColorIndex = 33
End If
If Target.Value = 4 Then
Target.EntireRow.Interior.ColorIndex = 45
End If
If Target.Value = 5 Then
Target.EntireRow.Interior.ColorIndex = 7
End If
If Target.Value = 6 Then
Target.EntireRow.Interior.ColorIndex = 3
End If
If Target.Value = 0 Then
Target.EntireRow.Interior.ColorIndex = xlNone
End If
End If
End Sub

"Otto Moehrbach" wrote:

If you want to change the entire row to red, you will need VBA. Put the
macro below in the sheet module for your sheet. To access that module,
right-click on the sheet tab, select View Code, and paste this macro into
that module. Click on the "X" in the top right corner to get back to your
sheet.
This macro will color the entire row red if the corresponding cell in
Column G goes to zero or less. It will remove the red color if the value
goes above zero. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
If Target.Value <= 0 Then
Target.EntireRow.Interior.ColorIndex = 3
Else
Target.EntireRow.Interior.ColorIndex = xlNone
End If
End If
End Sub

"Billjary" wrote in message
...
hi

Is it possible to highlight a row when a particular cell reaches zero??
For
example if my stock level of oranges in col G reaches zero after orders
are
placed then can i run something that will automatically change that row to
red?

Thanks

Adding colours as a function
 

Another possibility (without using VBA) is to select the row, then go to
conditional formatting. Then use "formula is"
=ISNUMBER(FIND("0",-absolute cell reference---)). Then Format,
Patterns, choose your color.


--
BruceP
------------------------------------------------------------------------
BruceP's Profile: http://www.excelforum.com/member.php...o&userid=33653
View this thread: http://www.excelforum.com/showthread...hreadid=529793

Adding colours as a function
 

I can only set 3 conditions w/ conditional formating but I need 7 different
colors.

Josh

"BruceP" wrote:


Another possibility (without using VBA) is to select the row, then go to
conditional formatting. Then use "formula is"
=ISNUMBER(FIND("0",-absolute cell reference---)). Then Format,
Patterns, choose your color.


--
BruceP
------------------------------------------------------------------------
BruceP's Profile: http://www.excelforum.com/member.php...o&userid=33653
View this thread: http://www.excelforum.com/showthread...hreadid=529793

Adding colours as a function
 

Josh
The macro I wrote for Billjary checks for the "Value" of "Target".
Target is the range of cells that were changed. Target can be multiple
cells, yes, but multiple cells do not have a "Value". That's why you get
the error.
To account for changing multiple cells at once, the macro will need to
be changed. Try the following. This will work with one cell as well as
multiple cells. "AColor" is the color index that you want for the
condition.
Note that this macro is triggered by a change in the contents of a cell in
Column G.
As written, this macro looks at each cell in the range Target. If the value
is zero (includes blank) the color index will be 1. If the value is <25,
the color index is 3. And so forth. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Range
Dim AColor As Long
If Target.Column = 7 Then
For Each i In Target
Select Case i.Value
Case 0: AColor = xlNone
Case Is < 25: AColor = 1
Case Is < 50: AColor = 3
Case Is < 75: AColor = 5
Case Is < 100: AColor = 7
Case Is < 125: AColor = 9
Case Is < 150: AColor = 11
Case Is < 175: AColor = 13
End Select
i.EntireRow.Interior.ColorIndex = AColor
Next i
End If
End Sub
"Josh" wrote in message
...
I can only set 3 conditions w/ conditional formating but I need 7 different
colors.

Josh

"BruceP" wrote:


Another possibility (without using VBA) is to select the row, then go to
conditional formatting. Then use "formula is"
=ISNUMBER(FIND("0",-absolute cell reference---)). Then Format,
Patterns, choose your color.


--
BruceP
------------------------------------------------------------------------
BruceP's Profile:
http://www.excelforum.com/member.php...o&userid=33653
View this thread:
http://www.excelforum.com/showthread...hreadid=529793

Adding colours as a function
 

My mistake. If the cell value is 0, the color index will be xlNone. Otto
"Otto Moehrbach" wrote in message
...
Josh
The macro I wrote for Billjary checks for the "Value" of "Target".
Target is the range of cells that were changed. Target can be multiple
cells, yes, but multiple cells do not have a "Value". That's why you get
the error.
To account for changing multiple cells at once, the macro will need to
be changed. Try the following. This will work with one cell as well as
multiple cells. "AColor" is the color index that you want for the
condition.
Note that this macro is triggered by a change in the contents of a cell in
Column G.
As written, this macro looks at each cell in the range Target. If the
value is zero (includes blank) the color index will be 1. If the value is
<25, the color index is 3. And so forth. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Range
Dim AColor As Long
If Target.Column = 7 Then
For Each i In Target
Select Case i.Value
Case 0: AColor = xlNone
Case Is < 25: AColor = 1
Case Is < 50: AColor = 3
Case Is < 75: AColor = 5
Case Is < 100: AColor = 7
Case Is < 125: AColor = 9
Case Is < 150: AColor = 11
Case Is < 175: AColor = 13
End Select
i.EntireRow.Interior.ColorIndex = AColor
Next i
End If
End Sub
"Josh" wrote in message
...
I can only set 3 conditions w/ conditional formating but I need 7
different
colors.

Josh

"BruceP" wrote:


Another possibility (without using VBA) is to select the row, then go to
conditional formatting. Then use "formula is"
=ISNUMBER(FIND("0",-absolute cell reference---)). Then Format,
Patterns, choose your color.


--
BruceP
------------------------------------------------------------------------
BruceP's Profile:
http://www.excelforum.com/member.php...o&userid=33653
View this thread:
http://www.excelforum.com/showthread...hreadid=529793

Adding colours as a function
 

Thank you Otto, very helpfull!

Josh

"Otto Moehrbach" wrote:

My mistake. If the cell value is 0, the color index will be xlNone. Otto
"Otto Moehrbach" wrote in message
...
Josh
The macro I wrote for Billjary checks for the "Value" of "Target".
Target is the range of cells that were changed. Target can be multiple
cells, yes, but multiple cells do not have a "Value". That's why you get
the error.
To account for changing multiple cells at once, the macro will need to
be changed. Try the following. This will work with one cell as well as
multiple cells. "AColor" is the color index that you want for the
condition.
Note that this macro is triggered by a change in the contents of a cell in
Column G.
As written, this macro looks at each cell in the range Target. If the
value is zero (includes blank) the color index will be 1. If the value is
<25, the color index is 3. And so forth. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Range
Dim AColor As Long
If Target.Column = 7 Then
For Each i In Target
Select Case i.Value
Case 0: AColor = xlNone
Case Is < 25: AColor = 1
Case Is < 50: AColor = 3
Case Is < 75: AColor = 5
Case Is < 100: AColor = 7
Case Is < 125: AColor = 9
Case Is < 150: AColor = 11
Case Is < 175: AColor = 13
End Select
i.EntireRow.Interior.ColorIndex = AColor
Next i
End If
End Sub
"Josh" wrote in message
...
I can only set 3 conditions w/ conditional formating but I need 7
different
colors.

Josh

"BruceP" wrote:


Another possibility (without using VBA) is to select the row, then go to
conditional formatting. Then use "formula is"
=ISNUMBER(FIND("0",-absolute cell reference---)). Then Format,
Patterns, choose your color.


--
BruceP
------------------------------------------------------------------------
BruceP's Profile:
http://www.excelforum.com/member.php...o&userid=33653
View this thread:
http://www.excelforum.com/showthread...hreadid=529793