+ Reply to Thread
Results 1 to 2 of 2

Conditional formatting

  1. #1
    Registered User
    Join Date
    01-19-2021
    Location
    united arab emirates
    MS-Off Ver
    2013
    Posts
    1

    Conditional formatting

    Hello.
    i have a question if any one can help me.
    i want to highlight values in three columns based on the criteria that one value in Column A is the sum of one value from column B & and one value from column C but values are not in same rows, and they are not linked, so i want to highlight all three cells, is there any formula that can find and highlight the all three values from three columns.

    thank you
    Attached Images Attached Images
    Last edited by KHANGUL01; 01-19-2021 at 11:48 PM.

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,557

    Re: Conditional formatting

    Hello KHANGUL01 and Welcome to Excel Forum.
    This proposal assumes that there will only be one value in A that will be the sum of one of the values in B and one of the values in C.
    This proposal employs a number of helper columns (F:L) which may be moved and/or hidden for aesthetic purposes.
    The values from C are transposed into row 1 using: =INDEX($D2:$D5,COLUMNS($A1:A1))
    The value in B and the value in C that sum to one of the values in A is identified using: =MATCH(SUM($C2,F$1),$A$2:$A$5,0)
    The conditional formatting for B is: =SUMPRODUCT(--ISNUMBER(F2:I2))
    The conditional formatting for C is: =SUMPRODUCT(--ISNUMBER(INDEX(F$2:I$5,,ROWS(A$1:A1))))
    In order to find the value in A two additional columns (K:L) are employed.
    Column K is populated using: =IF(SUMPRODUCT(--ISNUMBER(F2:I2)),C2,"")
    Column L is populated using: =IF(SUMPRODUCT(--ISNUMBER(INDEX(F$2:I$5,,ROWS(A$1:A1)))),D2,"")
    The conditional formatting for A is: =A2=SUM(K$2:K$5,L$2:L$5)
    For future reference you will usually get faster results if you upload an .xlsx file as opposed to a screenshot.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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: 6
    Last Post: 02-15-2018, 11:54 PM
  2. Macro to set up conditional message based on results of conditional formatting
    By paysola in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-04-2016, 12:38 AM
  3. [SOLVED] Conditional Statement for Negative Numbers inside a Conditional Formula
    By kuzniak in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 12-18-2015, 11:17 PM
  4. Replies: 2
    Last Post: 12-05-2014, 07:15 AM
  5. [SOLVED] Find maximum value in conditional range (conditional on the name on another column)
    By gjrr4x1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-26-2013, 06:51 AM
  6. Replies: 3
    Last Post: 05-15-2012, 04:13 PM
  7. Conditional Formatting Code - Deletes Wrong Conditional Format
    By RSpecianJr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-29-2010, 10:53 AM

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