Conditional Formatting help needed

[CHlEFS47]

This is probably something very simple for Excel users more experienced then I am.
I have a table of 'ranges' e.g. 70-75 which refers to square meters. I then have a row where I input something e.g. 71. I'd like it to conditionally be formatted that if it is within that range that it turns green. But I don't want to have an overly complicated way of doing it - can the conditional formatting tool pick up if it is between the range figure selected e.g. 70-75 or 85-90??

 

[Joe4]

Welcome to the Board!

You can just use the formula option and use Conditional Formatting formula of:

=AND(A2>=70,A2<=75)

for a value in cell A2.

 

[CHlEFS47]

Thanks for the response. The issue is i want it as a general conditional format that is based on the figures in the cell so 70-75 could be in one cell but in the next one it could be 80-85.

 

[Joe4]

I don't think your question is quite clear.
Are you saying that you want to Conditional Format it if it is between 70-75 or 80-85 (and have the same Conditional Formatting color, regardless of which one it is in between)?
If so, then you would just use a CF formula of:

=OR(AND(A2>=70,A2<=75),AND(A2>=80,A2<=85))

If you wanted different CF colors for each range, then you would make two rules, using the formula structure I gave you in the first reply for each range.

If neither of those are what you are after, please post some examples of what you want.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[CHlEFS47]

I don't think your question is quite clear.
Are you saying that you want to Conditional Format it if it is between 70-75 or 80-85 (and have the same Conditional Formatting color, regardless of which one it is in between)?
If so, then you would just use a CF formula of:

=OR(AND(A2>=70,A2<=75),AND(A2>=80,A2<=85))

If you wanted different CF colors for each range, then you would make two rules, using the formula structure I gave you in the first reply for each range.

Sorry no. To give you the scenario, it's a spreadsheet for evaluating houses. There's a drop down with floor area ranges e.g. 70-75, 75-80, 80-85 and so on. Then I enter a figure below it and if it is within that range e.g. 71 square meters is within the 70-75 range selected above it, then it turns green. Sorry! bit of an odd scenario

 

[dreid1011]

Something like this? Formula may need adjusting based on your version of Excel...

Book1
	A	B	C
1	Ranges (m2)	80
2	70-75		3
3	75-80
4	80-85
5	85-90
6	90-95
7	95-100
Sheet2

Cell Formulas
Range	Formula
C2	C2	=MATCH($B$1,LEFT($A$2:$A$7,FIND("-",$A$2:$A$7)-1)*1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A7	Expression	=MATCH($B$1,LEFT($A$2:$A$7,FIND("-",$A$2:$A$7)-1)*1)=ROW(A1)	text	NO

 

[Joe4]

A picture says 1000 words! It looks like dreid1011 may have figured out what you are after.
Images can be very helpful (especially since I am a very visual person).

Note: The nature of your data and how you have your range set up will keep you from having a "simple" answer.
You are trying to compare a number to a string ("70-75"). So your formula has to parse the string into usable numbers.
It could be "less" complicated" if your range was not in one cell, but two, with just single numbers in them (i.e. 70 in column A, and 75 in column B).