Adding colours as a function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

"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
 
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
 
Another possibility (without using VBA) is to select the row, then go t
conditional formatting. Then use "formula is
=ISNUMBER(FIND("0",-absolute cell reference---)). Then Format
Patterns, choose your color
 
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
 
Back
Top