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:


    =$A1="X"


    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


    Hi..


    Here's an alternative..


    Press the "Click!" button on sheet2.

    Code
    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
            .AutoFilter
        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!