Conditional formatting with validation dropdown - insert same smiley with different cell fill?

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
849
Office Version
  1. 365
Platform
  1. Windows
Hi

I've been draining my brain trying to work out how I can do this. I'm using data validation with a list/dropdown box with J, K and L options. The current colours are in sheet 'Training Log' L2:L4. The column is formatted as Wingdings so the cell in Col H shows either a happy, neither happy or sad, or unhappy smiley. The conditional formatting fills the cell Green for J, Gold for K and Red for L as below.

Validation Col.H
J
K
L

What I want to do is use the happy smiley (wingdings J) again with the letter R and yellow fill and I'm stuck, as conditional formatting relies on what's in the cell and the happy smiley can only be with the letter J.

I presume this can be done but with code instead? The cell being filled is Col H in the last filled row.

Help appreciated - many thanks!
 
Last edited:

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,908
Office Version
  1. 2007
Platform
  1. Windows
How about:
For the conditional formatting of the letter "R",
Click the Format button.
Select Fill Tab, select yellow colour for highlighting.
Select Number Tab. Select Custom Category.
In the Type field, write:
;;;"J"

1632450996973.png


Press OK

So you capture an R but the conditional formatting changes it to J, it puts a happy face and the color yellow. :)

1632451217429.png
 
Solution

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
849
Office Version
  1. 365
Platform
  1. Windows
Hey that's great, thanks ever so much Dante!

Can I just ask what ";;;" actually does please?

Thanks again!
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,101
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
It's all here....a semicolon is basically an IF statement
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
849
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@Michael M - many thanks, that's helpful. I wouldn't have known how to search for that.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,908
Office Version
  1. 2007
Platform
  1. Windows
Can I just ask what ";;;" actually does please?
In this case it is for the text format, the formats of the positives, negatives and zero are empty, since they are not required.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,908
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Edit:
I was missing this part:
Each ; separates between: {format for positive value}; {format negative val}; {format for 0}; {format for text}

In this case it is for the text format, the formats of the positives, negatives and zero are empty, since they are not required.
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
849
Office Version
  1. 365
Platform
  1. Windows
Thank you once again Dante, it was a brilliant solution.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,908
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,147,455
Messages
5,741,218
Members
423,649
Latest member
steel1968

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