Coloring within Ranges, Examples

Home page:
[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
  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 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))

Set rng = Range(ActiveCell,ActiveCell.End(xldown))


[usual diatribe about not having to "select" to work with a range of cells

**Tom Ogilvy 2000/03/16**
'Get the column letter
strColletter = Left(Columns(colNumber).Address(false,false), _
' 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).


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

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved