[excel] Excel - programm cells to change colour based on another cell

I am trying to create a formula for Excel whereby a cell would change colour based on the text in the previous cell.

So for example if cell B2 contains the letter X and then B3 is Y, I would like B3 to turn green.
Equally, if B2 contains X and B3 contains W I would like B3 to turn red.

Any ideas much appreciated!

This question is related to excel excel-formula conditional-statements

The answer is


Select ColumnB and as two CF formula rules apply:

Green: =AND(B1048576="X",B1="Y")

Red: =AND(B1048576="X",B1="W")

enter image description here


Use conditional formatting.

You can enter a condition using any cell you like and a format to apply if the formula is true.


  1. Select cell B3 and click the Conditional Formatting button in the ribbon and choose "New Rule".
  2. Select "Use a formula to determine which cells to format"
  3. Enter the formula: =IF(B2="X",IF(B3="Y", TRUE, FALSE),FALSE), and choose to fill green when this is true
  4. Create another rule and enter the formula =IF(B2="X",IF(B3="W", TRUE, FALSE),FALSE) and choose to fill red when this is true.

More details - conditional formatting with a formula applies the format when the formula evaluates to TRUE. You can use a compound IF formula to return true or false based on the values of any cells.


Examples related to excel

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel?

Examples related to excel-formula

Excel doesn't update value unless I hit Enter Referencing value in a closed Excel workbook using INDIRECT? Conditionally formatting cells if their value equals any value of another column Sum values from multiple rows using vlookup or index/match functions What does an exclamation mark before a cell reference mean? If two cells match, return value from third Excel - programm cells to change colour based on another cell Format numbers in thousands (K) in Excel Excel Formula which places date/time in cell when data is entered in another cell in the same row Excel formula to display ONLY month and year?

Examples related to conditional-statements

How to have multiple conditions for one if statement in python Excel - programm cells to change colour based on another cell Using OR & AND in COUNTIFS C error: Expected expression before int Conditional Replace Pandas SELECT query with CASE condition and SUM() Simpler way to check if variable is not equal to multiple string values? Replace all elements of Python NumPy Array that are greater than some value How can I check if a string only contains letters in Python? Check if year is leap year in javascript