+ Reply to Thread
Results 1 to 4 of 4

Conditional Formatting for cells that refer to other cells

  1. #1
    Registered User
    Join Date
    03-25-2024
    Location
    Dresden, Germany
    MS-Off Ver
    Microsoft 365
    Posts
    2

    Conditional Formatting for cells that refer to other cells

    Hello!

    In my table, I'm doing lots of calculations. I want to highlight all cells that contain a formula with reference to any other cell.

    E.g. my cells might have contents like this:

    Formula Highlighting
    =$A$1+$A$2 Yes
    =ISTEXT((C25)) Yes
    210 No
    =210 No
    =210+A5 Yes
    =210+35 No

    I tried so many approaches to analyze the formulatext, but I failed again and again.
    So maybe you can help with some good idea?

    Thank you!
    Max

  2. #2
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2308
    Posts
    1,474

    Re: Conditional Formatting for cells that refer to other cells

    Lets say your formulas you listed above are populated in cells E1:E6:

    Conditional Formatting Formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Images Attached Images
    <----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.

  3. #3
    Registered User
    Join Date
    03-25-2024
    Location
    Dresden, Germany
    MS-Off Ver
    Microsoft 365
    Posts
    2

    Re: Conditional Formatting for cells that refer to other cells

    Hi dosydos!

    Thank you very much, that's a very nice approach! And it might be good enough for my usecase
    But I guess it is not perfect, since functions would be highlighted wrongly as well. For example

    Formula Highlighting
    =FLOOR(210.15) No
    Your solution would highlight that cell, even though there is no reference to another cell.

  4. #4
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2308
    Posts
    1,474

    Re: Conditional Formatting for cells that refer to other cells

    Hi Max,
    You are correct, i hadn't considered that. This is the only other solution i could come up with. The only issue with this formula is that Excel functions that have a Letter followed by a number would be highlighted incorrectly.
    an example of a function i am referring to is =DAYS360() or =LOG10(). The conditional formatting would treat that as a cell reference to cell S3 and G1 respectively.
    Now there are very few functions in excel that use numbers in their name, and they are pretty uncommon unless you are in the engineering or statistical industry.

    As for the included formula. This will work with cell references and absolute cell references.
    I could not think of another way of showing that a letter and number combination is = to a cell reference other than the below formula which makes a list 468 in length concatenating A-Z,1-9, and $-"blank".

    Conditional Formatting Formula entered same way as my original answer:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by dosydos; 03-26-2024 at 02:48 PM.

+ 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: 5
    Last Post: 10-27-2022, 01:40 AM
  2. Replies: 2
    Last Post: 05-20-2022, 02:01 AM
  3. [SOLVED] Count the number of cells with specific color including conditional formatting cells
    By searchmaster2010 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-19-2022, 09:48 AM
  4. Replies: 3
    Last Post: 11-16-2016, 04:04 PM
  5. Replies: 5
    Last Post: 08-06-2013, 02:13 PM
  6. Replies: 5
    Last Post: 05-18-2013, 07:49 PM
  7. Conditional formatting, macros, or formula to fill in new cells of previous cells?
    By greenfox74 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-26-2012, 03:46 PM

Tags for this Thread

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