+ Reply to Thread
Results 1 to 5 of 5

Conditional format reference own cell?

  1. #1
    Registered User
    Join Date
    10-06-2015
    Location
    europe
    MS-Off Ver
    2013
    Posts
    13

    Conditional format reference own cell?

    I want to use conditional format to add borders to cells dynamically, like this:

    - If cells A or B arent empty, then add a grey border to the bottom of cells A:E
    - If cells in the next row of A or B are empty, then add a black border to the bottom of cells A:E

    The formula that goes in conditional formatting would be something like this, in pseudo code: OR(Ax!=0;Bx!=0), being Ax and Bx the cells in the row x, which I dont know how to reference.

    In the second case I should do the same but adding +1, like AND( OR(Ax!=0;Bx!=0); A(x+1)=0; B(x+1)=0 )

    Is it possible to do what I want? Maybe there is a different easier way? I just want to add lines to help reading between the rows, and add a thick line that indicates when its the last cell in the list.

    Thanks you

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Conditional format reference own cell?

    Definition of CF is top left cell. If you select range, eg. A1:D5 and in CF you will set =A1=5 (red) it will colour all cells in range which contain 5
    If set will be =$A1=5 it will colour only columns which contain 5
    If set will be =A$1=5 it will colour only rows which contain 5
    Easier will be if you attach example Excel workbook with what you have and what you want, with details.

  3. #3
    Registered User
    Join Date
    10-06-2015
    Location
    europe
    MS-Off Ver
    2013
    Posts
    13

    Re: Conditional format reference own cell?

    If you dont mind I attatch example instead of the orignal doc, but its basically what I want. There are 2 tables, the left one is what the user inputs. The right one is what it is supposed to look like, what the conditional format has to add. In this exemple I put the borders manually.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Conditional format reference own cell?

    First select your entire range that you want to conditionally format, let's say A1:E50

    Then Conditional Formatting> New Rule> Use Formula
    =OR($A1=0, $B1=0) Format border
    Repeat with Condition 2
    =OR($A2=0, $B2=0)
    I'm assuming you want the border on the active row, not at the bottom of your total range?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Conditional format reference own cell?

    With you example file.
    I assumed: if any of cell from the table (range A6:D10) is blank show lower border for the table (not for every cell)
    • so select range A10:D10
    • open CF and set formula: =OR($A6:$A10="",$B6:$B10="",$C6:$C10="",$D6:$D10="")
    • set format (lower border)
    • ok
    hope that helps

    ===
    If the basic problem is solved
    - it's always a good practice to show respect to the person(s) who have helped you
    - is a click on the Add Reputation first (left lower corner of the post of person(s) who helped you) and then
    - mark the thread as SOLVED (top right corner over your first post - Thread Tools). This is important for all of us,

    If you did it - ignore this part of the post, if not - just do it
    Thanks
    ===
    Attached Files Attached Files
    Last edited by sandy666; 04-07-2017 at 06:32 PM. Reason: file added

+ 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. [SOLVED] [B]Conditional Format How to Increment a cell reference[/B]
    By Vassellorry in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-10-2016, 07:47 PM
  2. Replies: 2
    Last Post: 03-02-2016, 07:05 AM
  3. [SOLVED] Conditional Format and Colour and Date Reference
    By yesmaybe in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-15-2012, 05:04 AM
  4. Conditional format with relative reference problem
    By lc130 in forum Excel General
    Replies: 10
    Last Post: 10-03-2012, 08:46 AM
  5. [SOLVED] How do i conditional format a row with reference from column?
    By radicrains in forum Excel General
    Replies: 3
    Last Post: 02-07-2012, 03:22 AM
  6. Replies: 5
    Last Post: 02-04-2011, 04:48 PM
  7. Conditional format from a reference cell?
    By oopsie poopsie in forum Excel General
    Replies: 4
    Last Post: 02-15-2006, 11:30 PM

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