IF Cell in column C is highlighted, then cell in column D = cell in column C

Brooklyn23

New Member
Joined
May 30, 2015
Messages
8
Hi all, please help,

Column C has rows of values. I want to populate column D as follows: IF Cell in column C is highlighted, then cell in column D = cell in column C, else leave the cell blank. Thank you.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

RudiS

Active Member
Joined
May 7, 2015
Messages
349
Hi,

When you say highlighted are you referring to fill colour? Is there a condition linked to cells that are highlighted? A formula cannot determine cell formatting but it can determine (test for) the value of the cell, so if the colour is due to some condition on the value in the cell the IF() function can work by testing the value of the cell without the need to test the colour. Can you provide more info regarding this?

TX
 

Brooklyn23

New Member
Joined
May 30, 2015
Messages
8
It is highlighted due to conditional formatting.

Conditional formating (already set up) I have two sets of data. If an ID in data set 1 appears in data set 2, then the row in data set 1 gets highlighted.

I then want to take the last column in data set 1 (we'll call it column F), which contains numbers, to be copied to the next column (column G) only if that column F is highlighted (via the conditional formatting). Here is an image:

<tbody>
</tbody>
http://1drv.ms/1Fnav2y (view picture 1 only)

Thank you.
 

RudiS

Active Member
Joined
May 7, 2015
Messages
349

ADVERTISEMENT

You can use the same conditional expression from your conditional formatting within the IF() statement. There is not need to test if the cell is highlighted.

For example:
Assume the conditional formatting is set up like this in the F column to test value in A1 with value in I1 (a second data set):
C/F: A1=I1, shade Yellow

Your IF() function:
In column G, type and IF function: =IF(A1=I1,F1,"")

As you can see, the same C/F condition is just slotted into the IF() functions logical test.
 

RudiS

Active Member
Joined
May 7, 2015
Messages
349
Based on the image in the Excel forum you can try a formula like this in the conditional formatting feature.

=COUNTIF($G$5:$G$11,RIGHT($B5,7))>0

G5:G11 are the unique ID numbers. (I recommend that this list contain the ACTUAL "cleaned up" ID numbers WITHOUT any leading zeros.)
RIGHT($B5,7) is the value to search for in range G5:G11
COUNTIF is the condition. If a match is found the logic is >0 and the condition is TRUE

The confition can be applied to the whole table to highlight the row where a match is found, or to the single ID column to highlight only the ID.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,310
Messages
5,641,450
Members
417,210
Latest member
rins

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top