+ Reply to Thread
Results 1 to 13 of 13

If left then Borders

  1. #1
    Forum Contributor
    Join Date
    09-24-2017
    Location
    Vic Australia
    MS-Off Ver
    Office 2013 Work / Office 365 Home
    Posts
    114

    If left then Borders

    Good afternoon

    I have been searching google for the last 40 mins
    and tried all different conditional formatting and cant seem to get one that works

    Column A has a bunch of text which cant be moved

    If Column B contains text Make Column C Have border Column C will all ways be blank

    I thought this would be a simple is notblank but it does not seem to like what I'm typing in

    On my sheet I'm working on if had columns From I all the way up to AA

    have attached a small sample

    any help would be great

    Thanks for your time
    JustinwB
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,333

    Re: If left then Borders

    But the values in B are numbers!

    However, you can use this formula CF rule. With C8 selected:

    =ISTEXT($B8)

    or:

    =ISNUMBER($B8)

    Set the formatting and the range it needs to apply to ($C$8:$C$20) in the dialog.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,535

    Re: If left then Borders

    You could use this in Conditional Formatting...
    =B8<>""
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Contributor
    Join Date
    09-24-2017
    Location
    Vic Australia
    MS-Off Ver
    Office 2013 Work / Office 365 Home
    Posts
    114

    Re: If left then Borders

    HI AliGW

    That makes sense being number over text Forgot how unforgiven excel if you dont do the right text/number

    I just worked out why it was not working When I selecting my new Rows I had $B$8 and not $B8 just noticed excel was adding the added $

    Is there a way to use the 1 CF for mult Columns or is it better to just do 1 CF for each new column have about 10 columns which would be 5 different CF

    Also thanks FDibbins =B8<>"" also works

    Thanks

    JustinwB

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,333

    Re: If left then Borders

    You can use one CF rule for multiple columns, depending on the layout. Sample workbook time!!!

    Excel is unforgiving because it doesn't do fuzzy logic.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,535

    Re: If left then Borders

    If all the other columns are based on column B, then just adjust the range to cover everything in the "Applies To' window
    BUT...then you will need to use =$B8<>""

  7. #7
    Forum Contributor
    Join Date
    09-24-2017
    Location
    Vic Australia
    MS-Off Ver
    Office 2013 Work / Office 365 Home
    Posts
    114

    Re: If left then Borders

    Hi AliGW

    I cant seem to get =ISNUMBER($B8) working
    not sure why I ended up using below

    using =B8<>"" works great with the added range

    JustinwB
    Attached Files Attached Files
    Last edited by justinwb; 02-15-2018 at 05:13 AM.

  8. #8
    Forum Contributor
    Join Date
    09-24-2017
    Location
    Vic Australia
    MS-Off Ver
    Office 2013 Work / Office 365 Home
    Posts
    114

    Re: If left then Borders

    Thanks for your help

    I ended up using

    =J1<>"" =$K$1:$DE$47
    No blanks =$J$1:$DE$47

    with

    Macro from another Question
    Please Login or Register  to view this content.
    I am able to convert Column A 1400 plus lines to a right Columns at 47 row down each which is Page size and then spam it across as many pages as is needed 10 pages set up on this sheet 2350 Rows worth
    The sheet also auto places boxes around all filled in Cells and then the cell to the right of it also fills in

    going to leave a completed sheet on here just in case any one else is ever trying to do the same thing
    as this took over 2 hours to get it working with help from a few people and changing a bit of information

    Thanks again for everybody help
    JustinwB
    Attached Files Attached Files
    Last edited by justinwb; 02-15-2018 at 05:11 AM.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,333

    Re: If left then Borders

    Explain what you mean. Use whichever solution works for you. I can't decipher what you perceive as wrong in your latest attachment.

    Got to go now - back later.

    By the way, you are breaking our forum rules: please add code tags round the code in your last post. Thanks.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,535

    Re: If left then Borders

    Thanks for the feedback

    I agree with Ali, if my suggestion worked, so should her's have also worked

  11. #11
    Forum Contributor
    Join Date
    09-24-2017
    Location
    Vic Australia
    MS-Off Ver
    Office 2013 Work / Office 365 Home
    Posts
    114

    Re: If left then Borders

    Very sorry AliGW

    its been long days at work

    after running a lot more tests
    when I tried =ISNUMBER($B8)
    it was filling boxes which had no numbers in other columns

    when I was using Column B was working great once I started to
    use different column it stoped working

    I forgot all about the $ at the start
    once I changed it to =ISNUMBER(B8)
    it worked great in all the other columns

    I did not notice =B8<>"" did not have the $ difference
    I will make sure to always look out for the $ when changing columns

    Thanks again for all the help and support

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,535

    Re: If left then Borders

    Happy top help

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,333

    Re: If left then Borders

    Yes, the use of the $ is crucial.

    $B$1 - will always refer just to B1.
    $B1 - column will be fixed at B, but row number will change depending on the row the cell is on (e.g. B2, B3, etc.).
    B$1 - row fixed, column floating (see above).
    B1 - both row and column will float.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 11-04-2016, 06:50 PM
  2. Auto Summing left within borders
    By GurungBoi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-17-2016, 12:13 PM
  3. [SOLVED] Auto change borders to thick borders
    By Pierce Quality in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-22-2013, 11:17 PM
  4. [SOLVED] How to hide cell reference borders (top/left side) in Excel?
    By jwe06 in forum Excel General
    Replies: 1
    Last Post: 08-03-2006, 11:45 AM
  5. [SOLVED] Cell Borders and "Columns to Repeat at Left"
    By DeLeo in forum Excel General
    Replies: 0
    Last Post: 07-28-2005, 11:05 AM
  6. top/left cell borders don't show
    By km in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-24-2005, 05:06 PM
  7. Cell borders & repeat columns at left re: printing
    By William DeLeo in forum Excel General
    Replies: 0
    Last Post: 03-23-2005, 10:40 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1