Adding colours as a function

[Billjary]

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

[Niek Otten]

Look in HELP for Conditional Formatting

--
Kind regards,

Niek Otten

"Billjary" <[email protected]> wrote in message news:[email protected]...
> 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

[Nospam]

Conditional formatting

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

"Billjary" <[email protected]> wrote in message
news:[email protected]...
> 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

[Miguel Zapico]

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

[Otto Moehrbach]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Billjary]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Billjary]

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

[Otto Moehrbach]

You have it right. I don't know how I managed to put in both lines. Otto
"Billjary" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > 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
>> >
>> >
>> >

[Josh]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[BruceP]

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.

[Josh]

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
>
>

[Otto Moehrbach]

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" <[email protected]> wrote in message
news:[email protected]...
>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
>>
>>

[Otto Moehrbach]

My mistake. If the cell value is 0, the color index will be xlNone. Otto
"Otto Moehrbach" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>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
>>>
>>>
>
>

[Josh]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >>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
> >>>
> >>>
> >
> >
>
>
>