Coloring within Ranges, Examples

Location: http://www.mvps.org/dmcritchie/excel/colorrng.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]

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

Coloring Ranges

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

Color lines with negative value in column 4 (col D)

This macro will color rows that have a negative value in Column D.

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 = xlNone

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

A 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

Similar results using Conditional Formatting

Jim Rech has presented a conditional formatting solution that provides similar results.

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.

Specifying Ranges in VBA code

See Topic: Working With Variable-Sized Ranges http://www.j-walk.com/ss/excel/tips/tip10.htm has a downloadable example simulating different key entries.

coding examples

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

Conditional Formatting Excerpts

Display Conditional Formatting formula used by Active cell

Frank Arendt-Theilen, Microsoft MVP Excel, 2000-10-16, Germany

Dim i as integer
With ActiveCell
For i = 1 To .FormatConditions.Count
    Debug.Print .FormatConditions(i).Formula1
Next i
End With

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

colors.htm, Excel color palette, color index, and other color information.
markcells in join.htm shows running through cells, and areas.
sumdata, Summarizing Data, and Auto Filter (an Overview)
David Hagar's Conditional Formatting issue in can be downloaded from Excel Experts E-Letter Archives page.
John Walkenbach's Working With Variable-Sized Ranges (tip 10).
John Walkenbach's Referring to Ranges in Your VBA Code (tip 20).
John Walkenbach's Creating a Worksheet Map (tip 28). The map is generated on a new worksheet, and it consists of color-coded cells that let you quickly identify values, text, and formulas.

You are one of many distinguished visitors who have visited my site here or in a previous location since opening on August 9, 1999 Return to TOP.

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