Let’s explore an advanced way to use Conditional Formatting in Excel. We’ll make a face move around the screen without any vba! Bonus: see how to interact with target cells!

(my finished Excel file)

 

The Challenge

 

Using only conditional formatting and formulas make this face move around an Excel sheet and interact with cells containing numbers.

(my previous post used vba to move around)

 

 

 

Solution Concept

Default Eye Position

• left eye: row 9 (cell N103), column 6 (cell P103)
• right eye: row 9 (cell N104), column 11 (cell P104)

 

 

 

 

 

 

Adjusting Default Eye Position

Clicking the arrow buttons assigns numbers to cells F102 and J102. Formulas in the green area adjust the starting point values based on the arrow button values.

 

Make the Eyes Move!

These conditional formatting rules are the final step to make the eyes move!

• =AND(ROW(A1)=$S$103,COLUMN(A1)=$W$103)
• =AND(ROW(A1)=$S$104,COLUMN(A1)=$W$104)

Change blue eye color to brown or green if you wish.

 

 

 

 

 

Excel File Moving Eyes

Click the arrows to see the eyes move! See how it works in this Excel file.

 

 

Add Mouth, Hair, Nose etc

Repeating the same steps we add Mouth, Hair, Sideburn left, Sideburn right, Nose and Face.

 

 

 

 

 

 

Now we can move the completed face using the arrows keys.

 

 

 

 

 

 

 

Interact With Target Cells

Challenge: trigger an action based on the face touching any cell with a number.

Concept: I ended up using formula based intersection method. Not easy but it worked!

 

Face Named Range

Here’s the dynamic named range for the moving face:

=OFFSET(Sheet1!$A$1,Sheet1!$AC$115-1,Sheet1!$AG$115-1,Sheet1!$S$115,Sheet1!$V$115)

 

List All Cells With Numbers

If I simply hard coded cell references to match cells with numbers then this works:

=SUM(I17 face)

This uses intersect method to determine if cell I17 and named range face intersect. As cell I17 would have a number in it I can further test if this intersection is >0.

But isn’t that lame? I want to allow you to add/remove cells with numbers.

Solution: I used a multi column approach to solve this in rows 126 to 141. You can enter numbers in 15 different cells. The message box lets you know whether or not the face intersects a cell with a number.

It works! The face interacts with cells that contain a number. The message box is basic but it proves that it’s possible!

 

 

 

 

 

 

 

 

 

 

Practical Application?

This is not very practical but it was a fun challenge and great practice!

99% of Excel users have no idea how powerful Excel is. Curiosity and determination enable creative solutions in Excel.

You could possibly create a quiz in Excel using this method. Cells could be different categories and intersecting a cell could display a random question from that category.

 

 

Update

Of course I kept playing around with this. Now the message box also displays the number from the intersecting cell.

Earlier I mentioned curiousity and determination. I forgot to mention that I broke one of my golden rules: no coffee after 4 pm!

 

 

About Me

I’m a Data Analyst and Excel enthusiast.

I live in Markham Ontario Canada (near Toronto).

Outside of Excel I enjoy hanging out with my dogs Cali and Fenton.