# Conditional Format to highlight "in between"



## still learning (Dec 28, 2022)

Hi

Happy New Year

Can I have a formula for conditional format that will highlight a cell that has a value that is between another value
F1 is the reference cell.  A1:B4 is the data cells
I tried using >>>>is between F1 and G1 ( I made a helper cell) but it doesn’t work

All numbers are formatted “General”
Excel 365
I tried 4 times to get A: the same width as B:. in the sample.... I'm giving up !!!!!!!!
mike
test.xlsxABCDEFG110015020220221502003200250I want A3 and B3  to have a red font 425030056I only need F1, But I made G1 to have something for "in between"Sheet1Cells with Conditional FormattingCellConditionCell FormatStop If TrueA1:B4Cell Valuebetween "F1" and "G1"textNO


----------



## xlhelp15 (Dec 28, 2022)

Hello - Welcome to MrExcel !

Please try this way to solve your query - "Conditional Formatting - New Rule - Format only cells that contain"


----------



## Sufiyan97 (Dec 28, 2022)

Is that what you want?

Book3ABCDEFG110015020025021502003200250I want A3 and B3  to have a red font 425030056I only need F1, But I made G1 to have something for "in between"Sheet2Cells with Conditional FormattingCellConditionCell FormatStop If TrueA1:B4Cell Valuebetween $F$1 and $G$1textNO


as per data there is nothing between 202 and 202 ?
How should it work can you please explain.


----------



## xlhelp15 (Dec 28, 2022)

So, you would need to highlight the values which is nearer to F1, right?


----------



## still learning (Dec 28, 2022)

Hi
I'm trying to get cells A3 and B3 highlighted since 202 (F1) is between 200 and 250.  I don't want anything else highlighted.  When I tried to use conditional format with >>in between<< I had to have two values., so I made a helper cell and put in the same value. I also tried to have G1 as =F1+1 to have a different value.
If I use >>less than and greater than <<  (two different conditions)    nothing gets highlighted
I even tried just A3 and B3 but nothing gets highlighted.

This is just a very short example of what my actual spreadsheet looks like.

I also tried >>contains, but since no cell in the "data" has 202, nothing is highlighted

I also tried to change "data" to just A1:A4, but that didn't work either
I tried using "use a formula..."
something like  =$F$1<,$F$1>
but it wasn't accepted. 
I'm guessing I'm using conditional format wrong or ...I'm  going crazy..isn't 202 in between 200 and 250  !!!!!!!

I just noticed that in the example the conditional format that is posted in XL2BB, cell format shows TEXT.  I double checked that the cells are formatted "general".
I also tried "number"
mike


----------



## Sufiyan97 (Dec 29, 2022)

Try

Book1ABCDEFG110015020220221502003200250I want A3 and B3  to have a red font 425030056I only need F1, But I made G1 to have something for "in between"78Sheet1Cells with Conditional FormattingCellConditionCell FormatStop If TrueA1:B4Expression=AND($F$1>=$A1,$F$1<=$B1)textNO


----------



## xlhelp15 (Dec 29, 2022)

Sufiyan97 said:


> Try
> 
> Book1ABCDEFG110015020220221502003200250I want A3 and B3  to have a red font 425030056I only need F1, But I made G1 to have something for "in between"78Sheet1Cells with Conditional FormattingCellConditionCell FormatStop If TrueA1:B4Expression=AND($F$1>=$A1,$F$1<=$B1)textNO


Glad you assisted - Thanks @Sufiyan97


----------



## still learning (Dec 29, 2022)

Thank you Sufiyan97
Thank you xlhelp15
That is what i was looking for

Happy New year

mike


----------



## Sufiyan97 (Dec 29, 2022)

You're welcome!
Glad we could help.


----------

