Change Google Sheets Cell Color Based on Text in Another Cell

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!

How to get the HTML color codes in Google Sheets - Plus every color in the color picker!
How to get the HTML color codes in Google Sheets - Plus every color in the color picker!

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.

How to change text case in Google Sheets with UPPER/ LOWER/ PROPER
How to change text case in Google Sheets with UPPER/ LOWER/ PROPER

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.

How Do You Highlight Cells of the Same Color - Teacher Tech with Alice Keeler
How Do You Highlight Cells of the Same Color - Teacher Tech with Alice Keeler

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

Customizing Alternating Colors of a Table in Google Sheets
Customizing Alternating Colors of a Table in Google Sheets

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

How to hide cells in Google Sheets
How to hide cells in Google Sheets

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

Google Sheets: Alternate Colors - Teacher Tech with Alice Keeler
Google Sheets: Alternate Colors - Teacher Tech with Alice Keeler

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.

Conditional Formatting Based on Another Cell in Google Sheets
Conditional Formatting Based on Another Cell in Google Sheets
How to Highlight Duplicated Cells in Google Sheets
How to Highlight Duplicated Cells in Google Sheets
The Ultimate Google Sheets Keyboard Shortcut Cheat Sheet for Beginners & Pros
The Ultimate Google Sheets Keyboard Shortcut Cheat Sheet for Beginners & Pros
How to Split Cells in Google Sheets
How to Split Cells in Google Sheets
How to Merge or Split Cells and Tables in Google Sheets
How to Merge or Split Cells and Tables in Google Sheets
How to Color Code in Google Sheets - ModernSchoolBus.com
How to Color Code in Google Sheets - ModernSchoolBus.com
How to Change Your Font in Google Sheets
How to Change Your Font in Google Sheets
how to make excel cells change color automatically based on date
how to make excel cells change color automatically based on date
How to Make Negative Numbers Red in Google Sheets - ModernSchoolBus.com
How to Make Negative Numbers Red in Google Sheets - ModernSchoolBus.com
the google sheets has hidden gems you're not using on your webpages
the google sheets has hidden gems you're not using on your webpages
Get Link to This Cell - Teacher Tech with Alice Keeler
Get Link to This Cell - Teacher Tech with Alice Keeler
Use Google Sheets to Create a Mobile App
Use Google Sheets to Create a Mobile App
Change the Gridline Color in Excel Spreadsheets - 2 Ways!
Change the Gridline Color in Excel Spreadsheets - 2 Ways!
How to Lock Cells in Google Sheets | ClickUp
How to Lock Cells in Google Sheets | ClickUp
How to Highlight Duplicates in Google Sheets
How to Highlight Duplicates in Google Sheets
How to Highlight a Row in Google Sheets Based on a Checkbox
How to Highlight a Row in Google Sheets Based on a Checkbox
three google sheets are shown with the words'3 google sheets formulas to know '
three google sheets are shown with the words'3 google sheets formulas to know '
How to Sort Columns Like a Pro in Google Sheets
How to Sort Columns Like a Pro in Google Sheets
Master Freezing Rows and Columns in Google Sheets – A Complete Beginner’s Guide! 📊
Master Freezing Rows and Columns in Google Sheets – A Complete Beginner’s Guide! 📊
Google Sheets Checkbox Strikethrough
Google Sheets Checkbox Strikethrough

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!

Related Articles

Can Turkey See Red How To Remove Sort By Color In Excel How To Fill Color In Excel Using Keyboard Shortcut Is Turkey Red How To Change Cell Color In Excel How To Auto Color Cells In Excel How To Change Color Of Top Row Or Header Of A Table In Google Sheets How To Change Cell Color In Google Sheets Based On Text Input How To Change Cell Color In Google Sheets Mobile How To Color Code Cells In Google Sheets