Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
if condition is met, shade cell [WORKS] but if value changes:
ty..that works..
but there is still a problem (err... "inconvinience" rather.) i might send this script to "non-excel literate folks" (like me ..but i kinda figured it a little) anywho... is there an easier way to activate this script than having to go in "View Code" ? a Second problem i'm having: my first column has existing data my 2nd column has data i manually input. and every other column after that has formulas, copied down, which auto-update cell values, based on what i enter in the B column. This code for shading cells works for the 2nd column. your code works for every other cell. BUT if i edit a value in column B (which changes stuff in teh other columns) the other columns's values dont update their color. its a real pain having to 'run' the script every single time i edit something. can i have it continuously running somehow? thanks in advance "Vergel Adriano" wrote: The code that you have fires only whenever a cell or range is modified in the spreadsheet. It will not 'process' any of the cells with existing data unless they are modified the user. To apply the color scheme to cells with data, try running a code like this Sub ColorCells() Dim icolor As Integer Dim c As Range For Each c In Range("A25:P344") Select Case c Case Is < 0 icolor = 3 Case 0 icolor = 51 Case 1 icolor = 45 Case 2 icolor = 4 Case 3 icolor = 10 Case 4 icolor = 5 Case 5 icolor = 48 Case 6 icolor = 9 Case Is 6 icolor = 3 Case Else icolor = 2 End Select c.Interior.ColorIndex = icolor Next c End Sub After that your Worksheet_Change code should take care of future changes. As for changing the data a 2nd or 3rd time, I did not get any error. To take care of when multiple cells are updated, try this slighlty modified version of your code: Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer Dim c As Range If Not Intersect(Target, Range("A25:P344")) Is Nothing Then For Each c In Target Select Case c Case Is < 0 icolor = 3 Case 0 icolor = 51 Case 1 icolor = 45 Case 2 icolor = 4 Case 3 icolor = 10 Case 4 icolor = 5 Case 5 icolor = 48 Case 6 icolor = 9 Case Is 6 icolor = 3 Case Else icolor = 2 End Select c.Interior.ColorIndex = icolor Next c End If End Sub -- Hope that helps. Vergel Adriano "Mo2" wrote: ok, thanks to this here site: http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm and help from u guys, i've figured out how to shade cells based on conditions. (I couldnt use Conditional Format becuz there was more than 3 conditions) anyhow... this piece of code works as it should. when a cell value is "1", its an orange color when a cell value is less than 0, the cell turns red. my problem is... this code doesnt apply to cells that already have "1" as a value, and so forth (the other cases mentioned below) why doesn't it ? and how i would i make it? also (if it isn't resolved by the first questions resolve) it gives me an error when i change a cell's value a 2nd or 3rd time (i.e. from value "-3" (cell color turns red) to "4" (gives an error) error also occurs when i delete a cell or paste something over multiple cells. here's my code..... thanks in advance if you can help me out Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A25:P344")) Is Nothing Then Select Case Target Case Is < 0 icolor = 3 Case 0 icolor = 51 Case 1 icolor = 45 Case 2 icolor = 4 Case 3 icolor = 10 Case 4 icolor = 5 Case 5 icolor = 48 Case 6 icolor = 9 Case Is 6 icolor = 3 Case Else icolor = 2 End Select Target.Interior.ColorIndex = icolor End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
if condition is met, shade cell [WORKS] but if value changes:
Unless you declared the Sub as Private, you should be able to run it by going
to Tools-Macro-Macros or by pressing Alt-F8. You would see a list of subroutines and you can run them from there. As for the second problem you're having, as long as you're working with a relatively small range, you can persist the color changes by refreshing the colors everytime a cell in the range is changed by calling the ColorCells sub. i.e.: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A25:P344")) Is Nothing Then ColorCells End If End Sub -- Hope that helps. Vergel Adriano "Mo2" wrote: ty..that works.. but there is still a problem (err... "inconvinience" rather.) i might send this script to "non-excel literate folks" (like me ..but i kinda figured it a little) anywho... is there an easier way to activate this script than having to go in "View Code" ? a Second problem i'm having: my first column has existing data my 2nd column has data i manually input. and every other column after that has formulas, copied down, which auto-update cell values, based on what i enter in the B column. This code for shading cells works for the 2nd column. your code works for every other cell. BUT if i edit a value in column B (which changes stuff in teh other columns) the other columns's values dont update their color. its a real pain having to 'run' the script every single time i edit something. can i have it continuously running somehow? thanks in advance "Vergel Adriano" wrote: The code that you have fires only whenever a cell or range is modified in the spreadsheet. It will not 'process' any of the cells with existing data unless they are modified the user. To apply the color scheme to cells with data, try running a code like this Sub ColorCells() Dim icolor As Integer Dim c As Range For Each c In Range("A25:P344") Select Case c Case Is < 0 icolor = 3 Case 0 icolor = 51 Case 1 icolor = 45 Case 2 icolor = 4 Case 3 icolor = 10 Case 4 icolor = 5 Case 5 icolor = 48 Case 6 icolor = 9 Case Is 6 icolor = 3 Case Else icolor = 2 End Select c.Interior.ColorIndex = icolor Next c End Sub After that your Worksheet_Change code should take care of future changes. As for changing the data a 2nd or 3rd time, I did not get any error. To take care of when multiple cells are updated, try this slighlty modified version of your code: Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer Dim c As Range If Not Intersect(Target, Range("A25:P344")) Is Nothing Then For Each c In Target Select Case c Case Is < 0 icolor = 3 Case 0 icolor = 51 Case 1 icolor = 45 Case 2 icolor = 4 Case 3 icolor = 10 Case 4 icolor = 5 Case 5 icolor = 48 Case 6 icolor = 9 Case Is 6 icolor = 3 Case Else icolor = 2 End Select c.Interior.ColorIndex = icolor Next c End If End Sub -- Hope that helps. Vergel Adriano "Mo2" wrote: ok, thanks to this here site: http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm and help from u guys, i've figured out how to shade cells based on conditions. (I couldnt use Conditional Format becuz there was more than 3 conditions) anyhow... this piece of code works as it should. when a cell value is "1", its an orange color when a cell value is less than 0, the cell turns red. my problem is... this code doesnt apply to cells that already have "1" as a value, and so forth (the other cases mentioned below) why doesn't it ? and how i would i make it? also (if it isn't resolved by the first questions resolve) it gives me an error when i change a cell's value a 2nd or 3rd time (i.e. from value "-3" (cell color turns red) to "4" (gives an error) error also occurs when i delete a cell or paste something over multiple cells. here's my code..... thanks in advance if you can help me out Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A25:P344")) Is Nothing Then Select Case Target Case Is < 0 icolor = 3 Case 0 icolor = 51 Case 1 icolor = 45 Case 2 icolor = 4 Case 3 icolor = 10 Case 4 icolor = 5 Case 5 icolor = 48 Case 6 icolor = 9 Case Is 6 icolor = 3 Case Else icolor = 2 End Select Target.Interior.ColorIndex = icolor End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
if condition is met, shade cell [WORKS] but if value changes:
wow
ok, it did work.. but.. As you said, its better for smaller ranges. and for my "A25:P324" range..it takes a few seconds to apply. QUESTION 1: is it possible to apply this cell shading code to just the following cells: D1 through D323 F1 through F323 H1 through H323 J1 through J323 L1 through L323 N1 through N323 P1 through P323 hopfeully, that will speed things up. QUESTION 2: Is there another way to activate the macro? for instance, the click of the word "ColorTheCells" in cell A1. and a way to deactivate the macro? (i'm trying to make this template sheet as little work as possible for ppl who've never used excel before) "Vergel Adriano" wrote: Unless you declared the Sub as Private, you should be able to run it by going to Tools-Macro-Macros or by pressing Alt-F8. You would see a list of subroutines and you can run them from there. As for the second problem you're having, as long as you're working with a relatively small range, you can persist the color changes by refreshing the colors everytime a cell in the range is changed by calling the ColorCells sub. i.e.: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A25:P344")) Is Nothing Then ColorCells End If End Sub -- Hope that helps. Vergel Adriano "Mo2" wrote: ty..that works.. but there is still a problem (err... "inconvinience" rather.) i might send this script to "non-excel literate folks" (like me ..but i kinda figured it a little) anywho... is there an easier way to activate this script than having to go in "View Code" ? a Second problem i'm having: my first column has existing data my 2nd column has data i manually input. and every other column after that has formulas, copied down, which auto-update cell values, based on what i enter in the B column. This code for shading cells works for the 2nd column. your code works for every other cell. BUT if i edit a value in column B (which changes stuff in teh other columns) the other columns's values dont update their color. its a real pain having to 'run' the script every single time i edit something. can i have it continuously running somehow? thanks in advance "Vergel Adriano" wrote: The code that you have fires only whenever a cell or range is modified in the spreadsheet. It will not 'process' any of the cells with existing data unless they are modified the user. To apply the color scheme to cells with data, try running a code like this Sub ColorCells() Dim icolor As Integer Dim c As Range For Each c In Range("A25:P344") Select Case c Case Is < 0 icolor = 3 Case 0 icolor = 51 Case 1 icolor = 45 Case 2 icolor = 4 Case 3 icolor = 10 Case 4 icolor = 5 Case 5 icolor = 48 Case 6 icolor = 9 Case Is 6 icolor = 3 Case Else icolor = 2 End Select c.Interior.ColorIndex = icolor Next c End Sub After that your Worksheet_Change code should take care of future changes. As for changing the data a 2nd or 3rd time, I did not get any error. To take care of when multiple cells are updated, try this slighlty modified version of your code: Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer Dim c As Range If Not Intersect(Target, Range("A25:P344")) Is Nothing Then For Each c In Target Select Case c Case Is < 0 icolor = 3 Case 0 icolor = 51 Case 1 icolor = 45 Case 2 icolor = 4 Case 3 icolor = 10 Case 4 icolor = 5 Case 5 icolor = 48 Case 6 icolor = 9 Case Is 6 icolor = 3 Case Else icolor = 2 End Select c.Interior.ColorIndex = icolor Next c End If End Sub -- Hope that helps. Vergel Adriano "Mo2" wrote: ok, thanks to this here site: http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm and help from u guys, i've figured out how to shade cells based on conditions. (I couldnt use Conditional Format becuz there was more than 3 conditions) anyhow... this piece of code works as it should. when a cell value is "1", its an orange color when a cell value is less than 0, the cell turns red. my problem is... this code doesnt apply to cells that already have "1" as a value, and so forth (the other cases mentioned below) why doesn't it ? and how i would i make it? also (if it isn't resolved by the first questions resolve) it gives me an error when i change a cell's value a 2nd or 3rd time (i.e. from value "-3" (cell color turns red) to "4" (gives an error) error also occurs when i delete a cell or paste something over multiple cells. here's my code..... thanks in advance if you can help me out Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A25:P344")) Is Nothing Then Select Case Target Case Is < 0 icolor = 3 Case 0 icolor = 51 Case 1 icolor = 45 Case 2 icolor = 4 Case 3 icolor = 10 Case 4 icolor = 5 Case 5 icolor = 48 Case 6 icolor = 9 Case Is 6 icolor = 3 Case Else icolor = 2 End Select Target.Interior.ColorIndex = icolor End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
shade one cell that will shade multiple cells | Excel Discussion (Misc queries) | |||
Help with the forumla, whether it works with IF condition or Vlook | Excel Worksheet Functions | |||
Shade Active Cell - Shade the cell the cursor is in only while in | Excel Programming | |||
auto-hide rows, cell format (# and @), update cell refs, shade cel | Excel Discussion (Misc queries) | |||
IF condition is true, shade cell (more than 3 conditions) | Excel Discussion (Misc queries) |