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