|This page contains macros and code snippets. Assistance to Install a Macro or User Defined Function|
Don't know if this is going to go much of anywhere I'm still trying to figure out joining object properties together I notice in XL2000 that sometime in the VB Editor you get hints as to what to type next, but it is does not pop up frequently enough, and I can't figure out exactly when it does appear. Anyway I was trying to put together some things on RANGE together here. The coloring of rows with negative values in a column caught my interest so I spent some time getting it work. Then Jim Rech posted an example using conditional formatting that does practically the same. Talking about conditional formatting don't forget to look at David Hagar's Conditional Formatting paper (see related area).
Sub ss02() 'The following will color interior color on all cells in sheet ActiveSheet.Cells.Interior.ColorIndex = RGB(255,200,100) 'The following will color interior color in used range in sheet ActiveSheet.UsedRange.Interior.ColorIndex = xlNone 'The following will color interior color on all cells in sheet ActiveSheet.selected.Interior.ColorIndex = xlNone End Sub
Resetting the interior color with a single statement eliminates the need for an ELSE in the macro should a row have been changed. Cells is used instead of UsedRange so that cells outside of the currentregion will be included. Formatting ALL cells will not affect the lastcell (Ctrl+End).
ActiveSheet.Cells.Interior.ColorIndex = xlNoneTurning off screenupdating and calculation will speed up the macro. They are and must be turned back on before leaving macro.
The lastcell is the intersection of the lastcell.column and lastcell.row and is the selected cell if you enter Ctrl+End.
Another way of coding for lastcell row and column would be as follows:
Set lastcell = Cells.SpecialCells(xlLastCell) LastRow = lastcell.row LastCol = lastcell.columnA text value such as -3 will be treated as a negative value as well as actual negative numbers.
Sub test() 'DMcRitchie 1999-08-07 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'XL97 & later startRow = 1 i = 1 Set ws = ActiveSheet maxRow = Cells.SpecialCells(xlLastCell).Row maxCol = Cells.SpecialCells(xlLastCell).Column ActiveSheet.UsedRange.Interior.ColorIndex = xlNone Do While i <= maxRow If ws.Cells(i, 4) < 0 Then _ ws.Range(Cells(i, 1), Cells(i, maxCol)).Interior.ColorIndex = 35 'light green i = i + 1 Loop Application.Calculation = xlCalculationAutomatic 'XL97 & later Application.ScreenUpdating = True End Sub
If you have Excel 97 or 2000 it might be easier to do this with conditional formatting.
For instance to format the entire sheet where Column 4 has a negative value. You would first select the entire sheet using the Select ALL button (Ctrl+A), then Format --> Conditional Formatting. Change Condition 1 to "Formula Is" and the condition to =$D1<0. It makes no difference if you put row number of 1 or 31 in in the formula.
To eliminate the conditional formatting use the delete or blank out the condition. Conditional formatting overrides cell formatting and attempts to select all cells and use Format cell will be ignored.
**Tom Ogilvy 1999/07/03** Set rng = range(Cells(1,"A"),Cells(1,"A").End(xldown)) rng.select Set rng = Range(ActiveCell,ActiveCell.End(xldown)) rng.select Range(ActiveCell,ActiveCell.End(xldown)).select [usual diatribe about not having to "select" to work with a range of cells suppressed] **Tom Ogilvy 2000/03/16** 'Get the column letter strColletter = Left(Columns(colNumber).Address(false,false), _ 1+(Colnumber>26)*(-1)) ' even simpler -- Dana DeLouis 2001/03/04 ' MsgBox Split(ActiveCell.Address, "$")(1) 'Column Letter ' MsgBox Split(activeCell.Address, "$")(2) 'Row Number
Dim i as integer With ActiveCell For i = 1 To .FormatConditions.Count Debug.Print .FormatConditions(i).Formula1 Next i End WithSee See example as a formula by Bernie Deitrick no loop involved. Must checkout whether/when loop is needed, of course, as a function you can only show one formula. (David).
Return to David McRitchie's My Excel Pages home page, or my Excel Site Index.
[Site Search -- Excel]
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved