+ Reply to Thread
Results 1 to 9 of 9

Conditional formatting of column based on DIFFERENT values in another column

  1. #1
    Registered User
    Join Date
    01-26-2022
    Location
    UK
    MS-Off Ver
    365
    Posts
    4

    Conditional formatting of column based on DIFFERENT values in another column

    Hello all,

    I have a table with two columns. Column A contains text strings. These require to be formatted either red or green.

    Column B has values varying from 1 to 10. I want the text in Column A to be formatted as per the value in their row within Column B. In this case, over 4 is green, under 5 is red.

    You can easily format based on a single value in a single cell but there are different values in Column B. This is where google gets stuck for me. I could do this based on a rule for each row, but want to do this all on one rule.

    Many thanks for anyone who can help.


    Cheers

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016 (preparing to update to 2021)
    Posts
    7,544

    Re: Conditional formatting of column based on DIFFERENT values in another column

    Hi, welcome to the forum.
    You select the entire column in A and under CF create a formula that checks column B then you set the condition and the format you want
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Registered User
    Join Date
    01-26-2022
    Location
    UK
    MS-Off Ver
    365
    Posts
    4
    Yes - my question is how to do this as I can't find the right tutorial on google
    Last edited by AliGW; 01-26-2022 at 03:59 AM. Reason: PLEASE don't quote unnecessarily!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,149

    Re: Conditional formatting of column based on DIFFERENT values in another column

    Administrative Note:

    Welcome to the forum.

    Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect (2112 is a release number, not the version per se). If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

    Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

    With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

    Thank you for helping us to help you.

    In this case, over 4 is green, under 5 is red.
    Is this the right way round???
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    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.

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016 (preparing to update to 2021)
    Posts
    7,544

    Re: Conditional formatting of column based on DIFFERENT values in another column


  6. #6
    Registered User
    Join Date
    01-26-2022
    Location
    UK
    MS-Off Ver
    365
    Posts
    4

    Re: Conditional formatting of column based on DIFFERENT values in another column

    Quote Originally Posted by AliGW View Post
    Is this the right way round???
    It can be any condition - my question is how to format cells on values which appear on the same row, in a different column. This means that the cell location of the condition will always be different.

    Quote Originally Posted by Keebellah View Post
    For starters start understand CF
    Thank you for your reply, however I already understand CF. From the link, you can use the ROW command to display a value, however it is only retrieved as an integer.

    Cheers

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,149

    Re: Conditional formatting of column based on DIFFERENT values in another column

    Let's say your column for highlighting is A and your column for conditions is D and you have a titles row in row 1.

    With A2 selected, create a rule using a formula in the CF dialog:

    =AND(D2<>"",D2>4)

    Set the formatting you want and then in the Applies to box for the rule, set the range to whatever you need (e.g. =$A$2:$A$1000).

    Create another rule:

    =AND(D2<>"",D2<=4)

    Set the formatting you want and then in the Applies to box for the rule, set the range to whatever you need (e.g. =$A$2:$A$1000).

    Your rules will know to increment the row number - you don't need to tell Excel that. Just make sure the Applies to range is correct.

    Try it, please. If you get unstuck, post a workbook here showing what you've done so far so that we can troubleshoot it for you.

  8. #8
    Registered User
    Join Date
    01-26-2022
    Location
    UK
    MS-Off Ver
    365
    Posts
    4

    Re: Conditional formatting of column based on DIFFERENT values in another column

    Quote Originally Posted by AliGW View Post
    With A2 selected, create a rule using a formula in the CF dialog:

    =AND(D2<>"",D2>4)
    Hello AliGW, thank you very much for helping. This was the formula that was needed. I was trying to find a tutorial or article that would help me do this myself but alas could not find one. Again many thanks for helping out, this was a little tricky to import but ended up great

    Cheers

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,149

    Re: Conditional formatting of column based on DIFFERENT values in another column

    Pleasure.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

+ 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] Conditional formatting based on sum of values with the same entries in another column
    By Mikaill in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-03-2022, 04:31 AM
  2. Conditional formatting based on old C-column values.
    By ghjk22 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-14-2019, 12:25 PM
  3. Replies: 6
    Last Post: 05-25-2018, 03:26 AM
  4. [SOLVED] Conditional Formatting of one cell based on values in a column
    By hammer68 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-23-2016, 06:12 AM
  5. [SOLVED] Conditional Formatting Rows based on Unique Values in Column
    By drjones5 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-13-2013, 04:27 PM
  6. conditional formatting based on a series of column text values
    By stewphil in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-19-2012, 03:34 AM
  7. Replies: 5
    Last Post: 09-24-2012, 05:14 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