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

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
1,069
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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
 
Upvote 0
Solution
Hey that's great, thanks ever so much Dante!

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

Thanks again!
 
Upvote 0
It's all here....a semicolon is basically an IF statement
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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
Back
Top