Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2008
    Posts
    78

    Unanswered: Contitional formatting

    I have a spreadsheet that has range of cells that contain two types of data. The cell will have the word "OnTime" or it will contain a number, or it will be blank.

    Starting at the first cell in the range, moving left to right, I want to do the following:

    Examine the cell
    If the cell is blank, move to the next cell in the range
    If the cell contains "OnTime", blank out the cell, make a border around it and shade it green
    If the cell contains a number, leave the number, make a border around it and shade it red

    Do this for every cell in the range.

    I know some macro code from recording then going back and tweaking it, but I don't know how to iterate through a range of cells, examine each, then do the above steps.

    Can anyone point me in the right direction?

    Thanks!!!

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Look in the Help file for For Each...Next loops.

  3. #3
    Join Date
    Aug 2008
    Posts
    78
    Quote Originally Posted by weejas
    Look in the Help file for For Each...Next loops.
    Yeah a few minutes after posting i found code that pointed me in the right direction. Google is much better than the help file

    The problem i'm having is the border is applying to the entire selection as opposed to the individual cells I iterate through.. just code tweaks at this point.. but thanks for the suggestion.. i wouldn't have thought to look in the help file.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Google is your friend!

    Why not post what you've got? I've used a few For Each...Next loops in the past, so I might spot something. It sounds like the difference between the parent range and the child cells is being missed out.

  5. #5
    Join Date
    Aug 2008
    Posts
    78
    Ugh I broke it. It makes everything Green now.. i took out the border code.


    Code:
    Sub FormatMyRange()
       Range("E16:H19").Select
           
       If TypeName(Selection) <> "Range" Then Exit Sub
           
       For Each cell In Selection
       
          If ActiveCell.Value = "OnTime" Then
             With Selection.Interior
                .ColorIndex = 4
                .Pattern = xlSolid
             End With
          End If
          
         
       Next cell
           
    End Sub

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Put the "Border" bit back in, and change the "Selection" in the "With" clause to "ActiveCell".

    As I mentioned, attach your file and we'll have a look at it.

  7. #7
    Join Date
    Aug 2008
    Posts
    78

    Green Red Transport

    Here's the macro.. it's almost working but it seems to not test the conditions right. This is the code but it's also in the attached file.

    The macro copies over some test values, and the worksheet itself shows what I'm trying to do...

    Thanks!@!!

    Code:
    Sub GreenRedTransport()
        Range("F4:H6").Select
        Selection.Copy
        Range("B4").Select
        ActiveSheet.Paste
        Range("B4:D6").Select
        
        
        For Each Cell In Selection
        
            Cell.Borders(xlDiagonalDown).LineStyle = xlNone
            Cell.Borders(xlDiagonalUp).LineStyle = xlNone
            With Cell.Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
            End With
            With Cell.Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
            End With
            With Cell.Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
            End With
            With Cell.Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
            End With
        
        
        
            If (Cell.Value > 0 And Cell.Value <> "ONTIME") Then
                With Cell.Interior
                    .ColorIndex = 3
                    .Pattern = xlSolid
                End With
                With Cell
                    .HorizontalAlignment = xlCenter
                    .Font.Bold = True
                End With
            End If
            
            If Cell.Value = "ONTIME" Then
                With Selection.Interior
                    .ColorIndex = 4
                    .Pattern = xlSolid
                End With
                ActiveCell.ClearContents
            End If
          
        Next Cell
        
    End Sub
    Attached Files Attached Files

  8. #8
    Join Date
    Aug 2008
    Posts
    78
    It's Working! Two lines changed here's the code:



    Code:
    
    Sub GreenRedTransport()
        Range("F4:H6").Select
        Selection.Copy
        Range("B4").Select
        ActiveSheet.Paste
        Range("B4:D6").Select
        
        
        For Each Cell In Selection
            
            Cell.Borders(xlDiagonalDown).LineStyle = xlNone
            Cell.Borders(xlDiagonalUp).LineStyle = xlNone
            With Cell.Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
            End With
            With Cell.Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
            End With
            With Cell.Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
            End With
            With Cell.Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
            End With
        
        
        
            If (Cell.Value > 0 And Cell.Value <> "ONTIME") Then
                With Cell.Interior
                    .ColorIndex = 3
                    .Pattern = xlSolid
                End With
                With Cell
                    .HorizontalAlignment = xlCenter
                    .Font.Bold = True
                End With
            End If
            
            If Cell.Value = "ONTIME" Then
                With Cell.Interior
                    .ColorIndex = 4
                    .Pattern = xlSolid
                End With
                Cell.Value = " "
            End If
          
        Next Cell
        
    End Sub

Posting Permissions

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