Need to add a touch of visual appeal to your Google Sheets data? You're in the right place! Today, we're going to unlock a powerful feature that allows you to change cell color based on text input in another cell. This color-coded system can significantly enhance readability and make your spreadsheets more engaging. Let's dive right in!

Before we start, ensure your Google Sheets is up-to-date, as the features might slightly vary depending on the version you're using. Now, let's explore two main methods to achieve this: using conditional formatting and script editing. Both methods are effective, but they cater to different user preferences and comfort levels, so let's dive into each.

Conditional Formatting: The Easiest Way
Conditional formatting is a built-in Google Sheets feature that allows you to apply formatting based on conditions. It's user-friendly, and you don't need any coding knowledge to master it. Let's explore how to use it to change cell color based on text input.

First, select the cells you want to format. Then, click on 'Format' in the menu, hover over 'Conditional formatting,' and click on 'Format cells if...'. This will open a new window with several options. We're interested in 'Format cells that contain...' and 'Text is exactly'. Here, you can input the text you want to match, and choose the formatting style, including fill color, from the right panel.
Applying Formatting to Specific Ranges

What if you want to apply color based on text input from another cell? In this case, you'll use a formula like "=B1='text'" where 'B1' is the cell you want to base your formatting on, and 'text' is the content you're looking for. This enables you to apply formatting dynamically, making your spreadsheet more interactive.
To do this, follow the same steps: select the cells, open the conditional formatting window, and under 'Format cells if...', choose 'Custom formula is'. Then, input your formula and choose the formatting style. Voila! Your cells should now change color based on text input in another cell.
Setting Priorities and Removing Formatting

You can apply multiple conditional formatting rules, but it's essential to set priorities to avoid conflicts. The rule with the highest priority (1) is applied first, and lower-priority rules only affect cells if the higher ones aren't met. To change priority, click on the three vertical dots (⋮) next to the rule and select 'Edit rule.'
To remove a rule, locate it under 'Manage rules,' and click on the three vertical dots (⋮) next to it. Select 'Delete rule' and confirm your choice. It's crucial to organize your rules properly to avoid confusion and ensure your formatting works as expected.
Script Editing: The More Advanced Way

If you're comfortable with coding, script editing offers more flexibility and customization options. It allows you to create intricate color-coding rules that might be impossible with conditional formatting alone. Let's explore how to do this.
First, click on 'Extensions' in the menu, then 'Apps Script' (or press Alt + Shift + S to quickly access it). This will open a new tab with your Google Sheets' script editor. Here, you can write custom functions to modify your spreadsheet's behavior, including changing cell color based on text input.




















Writing a Custom Function to Change Cell Color
You can create a function like the following one. Replace 'YourSheetName' with the name of your sheet and 'YourRange' with the range of cells you want to color-code. The function takes a cell's text value as an argument and applies different colors based on that text.
function color.CellsBasedOnText(cell) {
if (cell.getValue() === 'text1') {
cell.setBackground('yellow');
} else if (cell.getValue() === 'text2') {
cell.setBackground('lightblue');
} else {
cell.setBackground('white');
}
}
Applying the Custom Function to Your Cells
After creating your function, it's time to apply it. Click on 'Extensions' in the menu, then 'Apps Script,' and replace any code in the script editor with the one you've just created. Now, click on the disk icon to save your script.
To apply the function, click on the cell you want to base your formatting on and run the function. You can do this by clicking on 'Run' in the script editor menu or using the shortcut (Ctrl + Enter). The function will apply the formatting based on the cell's text value.
There you have it – you've now mastered two powerful methods to change cell color based on text input in another cell! Both of these techniques can significantly enhance your Google Sheets' data presentation. So go ahead, experiment with colors, and make your spreadsheets truly stand out! But remember, moderation is key – not every cell needs to be a different color! Happy coding!