Search for Cell Value and Draw Borders

  • Re: Search for Cell Value and Draw Borders

    Here is one way using Conditional Formatting, but note that Conditional Formatting doesn't offer bolded borders....

    Since you uploaded a .xls file, I will assume Excel 2003 version....

    Select column A and go to Format|Conditional Formatting,

    Select Formula Is from drop down and enter formula:


    click Format and choose from Border tab (top, bottom and left borders).

    Click Ok, then Ok again to finish

    Select columns C to whichever last column you think will ever contain the Z.

    Re-invoke Conditional Formatting and Formula Is with formula: =AND($A1="X",C1<>"",COUNTIF(C:C,"Z")=0) and Format:Borders (top and bottom only)

    click Ok once

    Click Add and then Formula Is: =AND($A1="X",COUNTIF(C:C,"Z")>0) and format|borders (top, bottom and right).

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Search for Cell Value and Draw Borders


    Here's an alternative..

    Press the "Click!" button on sheet2.

    Private Sub CommandButton1_Click()
        Application.ScreenUpdating = False
        With Range("A1").CurrentRegion
            .AutoFilter 1, "X"
            .Offset(1, 1).Resize(.Rows.Count - 1).SpecialCells(12).BorderAround ColorIndex:=1, Weight:=xlThick
        End With
        Application.ScreenUpdating = True
    End Sub
  • Re: Search for Cell Value and Draw Borders

    I stand and applaud your expertise. I apologize I should have sent the real desired results.

    See attached. Cell E2 will always be the top left corner.

    If a "X" appears in Column A , that row will not have dividers

    The Row Height and Column Width are determined by Column D and Row 2

    The cabinets could be 1-5 units but still is dependant on values in Row 2. So the right hand column could easily change to a higher Alphebetic letter.
    Also the if more drawers are needed the rows will reflect this making the bottom row change to a higher number.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!