Using OpenAI's ChatGPT to Update My Year in Pixels Excel Template

I've been seeing more and more videos on TikTok about this mysterious new artificial intelligence program called ChatGPT. People are saying it will eventually replace Google search. But what really got my attention was a demonstration of ChatGPT writing VBA macros for Microsoft Excel. Now I knew I had to try it for myself. 

I made a Year in Pixels Excel template to track my daily mood back in 2018 but haven't updated it lately. Since it's the start of a new year, I recently received some new comments and questions about how to use and modify the spreadsheet so I figured now would be a great time to update it and add a few new features. Then I had an idea - why don't I use this opportunity to update my mood tracking Excel template to try out ChatGPT? Let's give it a go!

How to have cell fill color conditional formatting change based on the color of another cell?

The request from a user that got me thinking was "how to easily change the colors in my year in pixels sheet?" An experienced Excel user could probably ascertain the colors are applied by conditional formatting formula rules. However, for users who are not Excel savvy, it's not very obvious and there should be an easier way to change the colors. As far as I know, there is none, or at least no easy way in my current version of Excel, to have the fill color in the conditional formatting reference another cell's color. So instead, I will have a macro update the conditional formatting rules at the click of a button. Let's jump over to ChatGPT.

How does ChatGPT work?

Here's the answer after asking it directly: 

GPT (Generative Pre-trained Transformer) is a type of language model developed by OpenAI that uses a transformer neural network architecture to generate text. GPT is trained on a large dataset of text and uses this training to generate new text that is similar in style and content to the training data.

ChatGPT is a variant of GPT that has been fine-tuned on a specific task, such as having conversations with humans. To fine-tune GPT for this task, ChatGPT is trained on a large dataset of conversation data and learns to predict the next word in a conversation based on the words that came before it.

When you input a message to ChatGPT, it processes the message and uses its knowledge of language and conversation patterns to generate a response. It does this by predicting the next word in the conversation based on the input it has received and the context of the conversation so far. The output of ChatGPT is then a response that is generated based on this prediction.

One thing to keep in mind right off the bat: ChatGPT is not always right. It says it right there on the main page under limitations: may occasionally generate incorrect information; may occasionally produce harmful instructions.

For example, I asked "list the ten tallest roller coasters in the world" which should be fairly easy and instead it produces a list that is incorrect and doesn't even make sense.

Not a great start but let's see how it does creating a macro from scratch.

Creating Custom VBA Macros Automatically with ChatGPT

I wasn't sure how specific I needed to be so I decided to err on the side of caution, plus I thought the more detailed I am the less modification I'll probably need to do later. Now I ask ChatGPT: "create a vba macro to make a new conditional formatting rule where range is C4 to N34, if cell value = 5 then change the fill color to the same color as in cell P12." Then it began typing and my jaw dropped.

I copied and pasted the code into Excel VBA editor and it worked! One thing I forgot is this macro will just add new rules applied to the same range. I need to delete all the existing formatting rules before adding new ones, so I ask ChapGPT:

I added the above code to the earlier response and linked it to a newly added button on my sheet. Now a user can change the fill color of a cell, click the button, and the fill colors update automatically to match.

If you want to see all this play out in real time, watch the video I recorded below:

Thoughts on the Future of ChatGPT

It's awesome that it doesn't just spit out the code, but it also suggests how you might need to modify it AND tells you how to run the macro as well. Even though I've only asked it to make simple macros, I already see how this program could save a lot of time.

I'm not done experimenting but so far ChatGPT seems like a much better option over Recoding macros or Google searches that might take you a few tries to find exactly what you're looking for. Especially when you can get custom code on the fly. On one hand, I feel a little obsolete, but on the other I also don't think ChatGPT will completely be replacing programmers just yet as you can see I still had to understand the code and modify it to fit my exact needs.

Download my Year in Pixels template for free here and try it for yourself. Open the macro editor to view the final codes written by ChatGPT.