Announcement

Collapse
No announcement yet.

Conditional Formatting row based on cell value

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Conditional Formatting row based on cell value

    Hello All:

    What I am trying to do is find a way to conditionally format a row based on a cell value. For example, if cell B11="0", the the range B11:J11 would be filled with white and the text also white. I have attached a sample for better explanation and/or help.

    Thanks
    Attached Files
    Last edited by AAE; May 3rd, 2011, 09:11. Reason: revise thread title

  • #2
    Re: Excel VBA Conditional Formatting row based on cell value

    This doesn't require VBA. You can use the built-in Conditional Formatting.

    Starting with cell B11 as the active cell, selected the range of cells from B11:J20
    Menu > Format > Conditional Formatting > Formula Option

    Formula: =$B11=0

    Set font and fill color as needed.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

    Comment


    • #3
      Re: Conditional Formatting row based on cell value

      That is correct, however, the cell ranges listed in the post are only examples. There are multiple rows that this would need to apply to. The sheets are populated as a trailer is loaded so this would be an "ending" step. It will also apply to approximately 13 different sheets.

      Comment


      • #4
        Re: Excel VBA Conditional Formatting row based on cell value

        Sorry, I left one part out. The forklift drivers are using OpenOffice (due to financial restrictions) and the desks are running Excel. With past tests of this, the built-in conditional formatting has "malfunctioned" between the two. I have had to go into the workbook and modifiy the rules each time.

        Comment


        • #5
          Re: Conditional Formatting row based on cell value

          In regards to the above question:

          I am trying to find a way to conditionally format on all sheets like "*1" (All sheets end with the number 1).

          Comment


          • #6
            Re: Conditional Formatting row based on cell value

            So far, what I have compiled is listed below:

            Code:
             
            Sub ClearOut()
            Dim ws As Worksheet
                For Each ws In ThisWorkbook.Worksheets
                    With ws
                        If .Name Like "*Bill" Then
                                If Cells.Range("B11").Value = 0 Then
                                    Cells.Range("B11:J11").Select
                                    Range("B11:J11").ClearContents
                                End If
                                If Cells.Range("B12").Value = 0 Then
                                    Cells.Range("B12:J12").Select
                                    Range("B12:J12").ClearContents
                                End If
                                If Cells.Range("B13").Value = 0 Then
                                    Cells.Range("B13:J13").Select
                                    Range("B13:J13").ClearContents
                                End If
                                If Cells.Range("B14").Value = 0 Then
                                    Cells.Range("B14:J14").Select
                                    Range("B14:J14").ClearContents
                                End If
                                If Cells.Range("B15").Value = 0 Then
                                    Cells.Range("B15:J15").Select
                                    Range("B15:J15").ClearContents
                                End If
                                If Cells.Range("B16").Value = 0 Then
                                   Cells.Range("B16:J16").Select
                                   Range("B16:J16").ClearContents
                                End If
                        End If
                    End With
                Next ws
                
            End Sub
            With this, for some reason, it will not loop through all sheets. I have attached the file I have been using. Could someone please assist.

            what I would really like to accomplish is have the macro check the value in cell B11, if the value is "0" then have it fill the range B11:J11 with white and color the font white. I would like this to go through cell B16 and do the same as above.MacroNeeded.xls

            Comment

            Working...
            X