Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Conditional formatting not working as expected

0

Hi

I've set up formatting so that when I type R in column C, the sum in column B turns blue. Suddenly, the sums in column B are turning blue when I type them in and not when I type R in C.

The formula I'm using is:

'New Rule' then 'Use a formula to determine which cells to format'.  I'm entering: 

=C2="R" and choosing blue font.

It used to work but now this is happening. Does anyone know what I've done wrong? 

Answer
Discuss

Discussion

Conditional formatting will not react to "when you type in column C". Instead, it will react to "the value in column C". So, after you type in "R" the value will be "R" and the total will remain blue for as long as the "R" remains in column C.
Note also that column C has many cells. As @Don has pointed out, if your conditional formatting instruction is to turn the total blue if $C$3 = "R" you might find that all rows turn blue when C3="R". But if your condition is that $CR = "R" (without the second $ sign) the condition will be applied only to the total in row 3.
Variatus (rep: 4889) Aug 6, '17 at 10:26 pm
Add to Discussion

Answers

0
Selected Answer

You may have copied a cell from column C into column B by accident or there may be other rules applied to cells in column B.

First, try using this formula:

=$C2="R"

Notice the dollar sign in front of C; that makes the reference absolute as far as columns are concerned, which means it will never change to B2 if you copy the cell elsewhere.

Next, select a cell in column B and go to Conditional Formatting > Manage Rules and make sure there are no other rules applied to those cells (it is very easy for this to accidentally happen).

Discuss

Discussion

Hi thanks for your thoughts. I'll go and take a look. I think hyou could be right.
Millie53 (rep: 2) Aug 10, '17 at 3:08 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login