conditional format 'IF'

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
I'd like a way to do this ....

If Subject1!C5 has the same date as ENTRY!V$2 then Subject1!F5 should be formatted with a black fill and display the text found in ENTRY!AB$2.

then if Subject1!C6 has the same date as ENTRY!V$2 then Subject1!F6 should be formatted with a black fill and display the text found in ENTRY!AB$2.

then if Subject1!C7has the same date as ENTRY!V$2 then Subject1!F7 should be formatted with a black fill and display the text found in ENTRY!AB$2.

ETC ETC ETC .. all the way down the page.

I don't seem to be permitted to do this with Conditional Formatting.

Is there another way ?

Kind regards,

Chris
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

liveinhope

Well-known Member
Joined
Dec 16, 2013
Messages
857
I've got this to work (sort of... ) In EXCEL 2007 at least conditional formatting cant refer to another worksheet or workbook . Not sure if any other version allows this

- in cell $M$1 on SUBJECT1 I entered a formula = 'entry'!$v$2

- formula in column F =IF(C2=$M$1,entry!$AB$2,"")

- then conditional format on column F use "formulal" option =$C2=$M$1 . ( then set up the format as fill black)
- format column F cells with font colour other than black
 

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
great idea, but I already have text written into cells in column F so the conditional format would have to be an 'over-ride' where it replaces whatever is already written into that cell with what is in ENTRY!AB2

any other ideas ?
 

liveinhope

Well-known Member
Joined
Dec 16, 2013
Messages
857
So what you want to do is to conditionally replace the values in column F and then format the cell as black fill, white font
as below ?


If Subject1!C5 has the same date as ENTRY!V$2 then Subject1!F5 should be formatted with a black fill and display the text found in ENTRY!AB$2.
If not then leave value in subject1!f5 and the formatting of subject1!f5 as is

then if Subject1!C6 has the same date as ENTRY!V$2 then Subject1!F6 should be formatted with a black fill and display the text found in ENTRY!AB$2.
If not then leave value in subject1!f6 and the formatting of subject1!f6 as is

<COLGROUP><COL style="WIDTH: 593pt; mso-width-source: userset; mso-width-alt: 28928" width=791><TBODY>
</TBODY>

then if Subject1!C7has the same date as ENTRY!V$2 then Subject1!F7 should be formatted with a black fill and display the text found in ENTRY!AB$2.

If not then leave value in subject1!f7 and the formatting of subject1!f7 as is

<COLGROUP><COL style="WIDTH: 593pt; mso-width-source: userset; mso-width-alt: 28928" width=791><TBODY>
</TBODY>
 

liveinhope

Well-known Member
Joined
Dec 16, 2013
Messages
857
can you use/add another column

- leave column F "as is "
- put (amended) formula =IF(C2=$M$1,entry!$AB$2,F2 ) in the "new " column (I used "G")
- then enter the conditional formatting formula column "G" (white font , black fill )
- you will/may also need a 2nd conditional format for "G" to display the unchanged values as black font , white fill
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,962
Members
413,954
Latest member
mrsandy

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