SOLVED

# Using Conditional Formatting with two types of data

Occasional Contributor

# Using Conditional Formatting with two types of data

I am trying to come up with a formula to highlight a cell when it is less than a number and column F = Service B.  I have tried this formula "=AND(\$F:\$F="Service B",\$E:\$E,30000)" but it does not work.  I am at a loss.  I need to highlight numbers in column E that are less than 27,000 but greater than 33,000 and also have the word Service B in column F.  I also need to do the same for Service A but at smaller values, which once I get the formula correct for service B I should have no problem creating one for service A.

12 Replies

# Re: Using Conditional Formatting with two types of data

*correction* The formula I am currently using is =AND(\$F:\$F="Service B",\$E:\$E<30000). I realized the one above does not have <. I also realize I am only using < and not > as well. I was just trying to get the formula to work before I added the >.

# Re: Using Conditional Formatting with two types of data

If you are trying to do this to a Table, then you have to do it like this.

``=IF(AND(\$F2="Service B",\$E2<30000),1,0)``

Then for "Applies to", enter the range you want it to happen to.

# Betreff: Using Conditional Formatting with two types of data

Here is an example file with two different examples.

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

# Re: Using Conditional Formatting with two types of data

@Erut313 That worked, thank you.  What is the reason for putting the 1,0 at the end of the formula?

# Re: Using Conditional Formatting with two types of data

@Erut313 I thought it worked but I am having issues now.  I changed your formula to this, so it would pick up anything greater than 33,000.  If you look at the screenshot, row 11 is in red although column f says Service A and the value is not greater than 33000.  Row 12 column E is not in red even though column f says Service B and the value is greater than 33,000.

# Betreff: Using Conditional Formatting with two types of data

=AND((A11>33000),(E12<33000))

# Re: Using Conditional Formatting with two types of data

@TheBoss86 The 1 and 0 are to activate the conditional formatting. Please send me a screenshot of the values that you chose. Sometimes the Formula will get messed up and instead of \$F2 it will say something like \$F124034, Make sure that doesnt say that.

# Re: Using Conditional Formatting with two types of data

@Erut313The screenshot I attached has the formula that is being used.

# Re: Using Conditional Formatting with two types of data

Take a screenshot like mine, so I can see your "Applies To"

# Re: Using Conditional Formatting with two types of data

@Erut313 Here is what I got.

best response confirmed by TheBoss86 (Occasional Contributor)
Solution

# Re: Using Conditional Formatting with two types of data

@TheBoss86 Here you go. I have it as less than, not greater than.

# Re: Using Conditional Formatting with two types of data

Thank you for the help!!!!! Got it to work.