Thread: Colourcode based on value range

Hello cocohead,

Welcome to the Forum!

This macro will prompt you for both the lower and upper limits. You can run this macro on any sheet in your workbook.

'Thread:  http://www.excelforum.com/excel-programming/783105-colourcode-based-on-value-range.html
'Poster:  cocohead
'Written: July 06, 2011
'Author:  Leith Ross

Sub ColourCodeValues()

  Dim Ans As Variant
  Dim Cell As Range
  Dim loLimit As Double
  Dim hiLimit As Double
  
EnterLoLimit:
    Ans = InputBox("Enter the lower limit value.")
    If Ans = "" Then Exit Sub
    
    On Error Resume Next
      loLimit = CDbl(Ans)
      If Err <> 0 Then
         MsgBox "You did not enter a valid number. Try again"
         GoTo EnterLoLimit
      End If
    On Error GoTo 0
    
EnterHiLimit:
    Ans = InputBox("Enter the upper limit value.")
    If Ans = "" Then Exit Sub
    
    On Error Resume Next
      hiLimit = CDbl(Ans)
      If Err <> 0 Then
         MsgBox "You did not enter a valid number. Try again"
         GoTo EnterHiLimit
      End If
    On Error GoTo 0
    
      For Each Cell In ActiveSheet.UsedRange
        If Cell < loLimit Or Cell > hiLimit Then
           Cell.Font.ColorIndex = 3     'Red
        End If
      Next Cell
    
End Sub

Adding the Macro (Excel 95 - 2003)

1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
   
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Hello cocohead,

This version of the macro will exclude all cells in columns "A:B" from being coloured.

'Thread:  http://www.excelforum.com/excel-programming/783105-colourcode-based-on-value-range.html
'Poster:  cocohead
'Written: July 06, 2011
'Updated: July 07, 2011 - Excluded columns "A:B" from being coloured
'Author:  Leith Ross

Sub ColourCodeValues()

  Dim Ans As Variant
  Dim Cell As Range
  Dim loLimit As Double
  Dim hiLimit As Double
  Dim Rng As Range
  
EnterLoLimit:
    Ans = InputBox("Enter the lower limit value.")
    If Ans = "" Then Exit Sub
    
    On Error Resume Next
      loLimit = CDbl(Ans)
      If Err <> 0 Then
         MsgBox "You did not enter a valid number. Try again"
         GoTo EnterLoLimit
      End If
    On Error GoTo 0
    
EnterHiLimit:
    Ans = InputBox("Enter the upper limit value.")
    If Ans = "" Then Exit Sub
    
    On Error Resume Next
      hiLimit = CDbl(Ans)
      If Err <> 0 Then
         MsgBox "You did not enter a valid number. Try again"
         GoTo EnterHiLimit
      End If
    On Error GoTo 0
    
    'Exclude cells in columns "A:B"
     With ActiveSheet
       Set Rng = Intersect(.UsedRange, .Range(.Columns(3), .Columns(Columns.Count)))
     End With
     
      For Each Cell In Rng.Cells
        If Cell < loLimit Or Cell > hiLimit Then
           Cell.Font.ColorIndex = 3     'Red
        End If
      Next Cell
    
End Sub