Highlight cells below specific value using Conditional Formatting

Yamasaki450

New Member
Joined
Oct 22, 2021
Messages
47
Office Version
  1. 2021
Platform
  1. Windows
Hello guys.

I would like to highlight cells below specific value in column.

Example:
I have some random numbers in column A

Like this

1
5
8
3
4
8
7
2
8
1

Now i would like to highlight all numbers below number 8. In this case this would be 3,7 and 1

How can i do that using Conditional Formatting function?

Hope u know what i want and sorry for my English :)

Thanks.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Select the range then click " Conditional Formatting" - Highlight Cell Rules - Less Than - and enter value and formatting
 
Upvote 0
This will highlight all numbers smaller than 8... Thats not what i need... I want to highlight only first cell below all 8s in column.

Probably i need formula for this?

Should look like this.
 

Attachments

  • Clipboard01.png
    Clipboard01.png
    12.3 KB · Views: 2
Upvote 0
You can do that by offsetting the ranges like
Fluff.xlsm
A
1
21
35
48
53
64
78
87
92
108
111
12
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A12Expression=A1=8textNO
 
Upvote 0
Solution
Sorry but how did you do that?...I'm complete noob in excel :). Can you explain this a bit better please? if u have time... All i want is to highlight or "paint" first cell below all 8's in column. That's it.
 
Upvote 0
If you select A2 down to the end of the data & then in CF select new rule, use a formula & enter in the formula.
 
Upvote 0
Got it working hehe :) Thanks a lot for your help. Have a nice day and sorry for me being dumb heh :)
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0
Hi im back with another problem.

I would like to sort all highlighted cells on top. But it wont work because formulas are inside.
Is there solution for this?

Like this:
 

Attachments

  • Clipboard02.png
    Clipboard02.png
    33.5 KB · Views: 2
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top