Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Conditional Formatting based on adjacent cells

This is a discussion on Conditional Formatting based on adjacent cells within the Excel Questions forums, part of the Question Forums category; I'm trying to apply a conditional formatting based on the value, not of the cell itself, but of an adjacent ...

  1. #1
    Board Regular
    Join Date
    Apr 2010
    Posts
    186

    Default Conditional Formatting based on adjacent cells

    I'm trying to apply a conditional formatting based on the value, not of the cell itself, but of an adjacent cell.

    The formula I have in my conditional format (to see if the cell is blank):
    =IF(OFFSET(C26,0,1)="",TRUE)

    But it doesn't seem to work. Any ideas?

  2. #2
    xld
    xld is offline
    Banned
    Join Date
    Feb 2003
    Location
    The Kingdom of Wessex
    Posts
    5,378

    Default Re: Conditional Formatting based on adjacent cells

    Just use

    =D26=""

  3. #3
    xld
    xld is offline
    Banned
    Join Date
    Feb 2003
    Location
    The Kingdom of Wessex
    Posts
    5,378

    Default Re: Conditional Formatting based on adjacent cells

    Quote Originally Posted by VoG View Post
    Try

    =IF(OFFSET(C26,0,1))=""

    That's not even valid.

  4. #4
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,619

    Default Re: Conditional Formatting based on adjacent cells

    Quote Originally Posted by xld View Post
    That's not even valid.

    Deleted
    HTH, Peter
    Please test any code on a copy of your workbook.

  5. #5
    Board Regular
    Join Date
    Apr 2010
    Posts
    186

    Default Re: Conditional Formatting based on adjacent cells

    >> xld:
    Didn't work

    >> VoG:
    Gives a syntax error unless I add ,TRUE for some reason. Ive done if statements with only the condition in conditional formatting before, and its worked, but it doesn't seem to work with adjacent cells >_<

    Excel 2003 btw.

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,786

    Default Re: Conditional Formatting based on adjacent cells

    Asator

    How didn't xld's suggestion work and what in words is the condition for the formatting?

    Is it only one cell you want to format?
    If posting code please use code tags.

  7. #7
    Board Regular
    Join Date
    Apr 2010
    Posts
    186

    Default Re: Conditional Formatting based on adjacent cells

    with =d26="" in the conditional format for c26, and d26 blank, the format is not applied. I tried putting some stuff in d26, then deleting it. No change.

    I then tried going at it a different way, by setting "not equal to" =d26="x". Immediately the format was applied whether or not d26 was equal to "x".

    So basically, its not working

    Ideally, I'd like to have a list of cells, that have a conditional format applied if the cell adjacent to it is left blank. I also have another sheet I'm working with that selects the lowest value from an array and highlights it with a conditional format. Is it possible to have that conditional format also applied to adjacent cells?
    I.e.
    array A1-A10, A6 is highlighted from being the lowest value. Is it possible to have it automatically format B6 as well with the same formatting, regardless of what the value in B6 is?
    Last edited by Asator; Jun 25th, 2010 at 03:45 PM.

  8. #8
    Board Regular
    Join Date
    Mar 2010
    Location
    seattle
    Posts
    394

    Default Re: Conditional Formatting based on adjacent cells

    Quote Originally Posted by Asator View Post
    I'm trying to apply a conditional formatting based on the value, not of the cell itself, but of an adjacent cell.

    The formula I have in my conditional format (to see if the cell is blank):
    =IF(OFFSET(C26,0,1)="",TRUE)

    But it doesn't seem to work. Any ideas?
    try this:

    =INDIRECT(ADDRESS(ROW(),4))=""
    I want Excel and Access to get married and have a baby. And I would call that baby 'Accel'

    Windows 7, Office 2010

  9. #9
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,786

    Default Re: Conditional Formatting based on adjacent cells

    Are you 100% sure D26 is empty?

    Is the value in there a result of a formula?
    If posting code please use code tags.

  10. #10
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,619

    Default Re: Conditional Formatting based on adjacent cells

    Sheet15

     CD
    26  
    27x 

    Conditional formatting
    CellNr.: / ConditionFormat
    D261. / Formula is =C26=""Abc


    Excel tables to the web >> Excel Jeanie HTML 4
    HTH, Peter
    Please test any code on a copy of your workbook.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com