Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Conditional Formatting row based on cell value

  1. #1
    Join Date
    1st February 2011
    Posts
    13

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by AAE; May 3rd, 2011 at 09:11. Reason: revise thread title

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    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

  3. #3
    Join Date
    1st February 2011
    Posts
    13

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    1st February 2011
    Posts
    13

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    1st February 2011
    Posts
    13

    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).

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    1st February 2011
    Posts
    13

    Re: Conditional Formatting row based on cell value

    So far, what I have compiled is listed below:

    VB:
     
    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 2
    Last Post: March 29th, 2011, 18:34
  2. Replies: 3
    Last Post: November 4th, 2010, 09:07
  3. Conditional formatting based on another cell
    By vbakke in forum EXCEL HELP
    Replies: 3
    Last Post: January 16th, 2006, 02:59

Bookmarks

Posting Permissions

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