Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA function for "Mean" using Array as argument | Excel Worksheet Functions | |||
Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
Adding superscript in text function | Excel Worksheet Functions | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
Need Function for adding rows on multiple sheets... struggling rookie ;) Thanks. | Excel Worksheet Functions |