Consulting

Results 1 to 10 of 10

Thread: Color cells based on values

  1. #1
    VBAX Regular
    Joined
    Dec 2006
    Posts
    30
    Location

    Color cells based on values

    In my sheet I would like to color all cells in the rows starting with "Rep:" (column A) red if the value in column F until the end is greater than 168 and green if the value of these cells is greater than or equal to 0. Nothing should change if the cell is empty.
    I have manually colored the first row (row 5) as an example (see attachment).

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,247
    Location
    You need conditional formatting. See http://www.contextures.com/xlCondFormat01.html
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    I was looking at this too and trying to get a whole column to conditionally format the #DIV/0! but can't seem to get it to work.

    Condition 1:
    Formula Is =ISERROR(C2) (White Font)

    Works fine for the single cell but should this work for a range e.g.

    Condition 1:
    Formula Is =ISERROR(C:C) (White Font)

    I have a book where formulas are in columns in the sheet code. When i copy and paste infos in to the sheet not all the columns are populated with figures which returns a lot of #DIV/0!s which I have to blank manually. The above would be very useful if it worked?

    Any ideas?

    thanks

  4. #4
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Blackie,
    Set it for one cell, then use Format Painter to apply it to the column
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,247
    Location
    No, select the whole column starting at C2, then add your first formula.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    VBAX Regular
    Joined
    Dec 2006
    Posts
    30
    Location
    I tried conditional formatting but that didn't work because it's only applicable for those rows that start with "Rep:". So I need VBA to find these rows in my report (it's a dynamic report) and then set the conditional formatting for all columns from F until the end of the report.

    Any suggestions?

  7. #7
    Seems to work by installing it in one cell and dragging down.

    Is there any way this can be installed in to a worksheet change event for specific columns e.g. cols J, S & U in sheet 1.?

    Would be better than having to continually copy the formatting.

    thanks for help

    Jon

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,247
    Location
    Quote Originally Posted by blackie42
    Is there any way this can be installed in to a worksheet change event for specific columns e.g. cols J, S & U in sheet 1.?
    [vba]

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Const WS_RANGE As String = "H1:H10" '<== change to suit

    On Error GoTo ws_exit
    Application.EnableEvents = False

    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
    With Target
    .FormatConditions.Delete
    .FormatConditions.Add _
    Type:=xlExpression, _
    Formula1:="=ISERROR(" & .Address(False, False) & ")"
    .FormatConditions(1).Interior.ColorIndex = 3 'Red
    End With
    End If

    ws_exit:
    Application.EnableEvents = True
    End Sub
    [/vba]

    This is worksheet event code, which means that it needs to be
    placed in the appropriate worksheet code module, not a standard
    code module. To do this, right-click on the sheet tab, select
    the View Code option from the menu, and paste the code in.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,247
    Location
    Quote Originally Posted by paddy69
    I tried conditional formatting but that didn't work because it's only applicable for those rows that start with "Rep:". So I need VBA to find these rows in my report (it's a dynamic report) and then set the conditional formatting for all columns from F until the end of the report.

    Any suggestions?
    [vba]

    Public Sub ProcessData()
    Const TEST_COLUMN As String = "A" '<=== change to suit
    Dim i As Long
    Dim LastRow As Long
    Dim LastCol As Long

    With ActiveSheet

    LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
    For i = 1 To LastRow

    LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column

    With .Cells(i, "F").Resize(, LastCol - 5)
    .FormatConditions.Delete
    .FormatConditions.Add _
    Type:=xlExpression, _
    Formula1:="=LEFT($A" & i & ",4)=""Rep:"""
    .FormatConditions(1).Interior.ColorIndex = 3 'Red
    End With
    Next i

    End With

    End Sub
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  10. #10
    Thanks for help XLD - can't close Thread as I pretty much hi-jacked it.

    thanks again

    jon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •