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

How to scan a range to identify cells in credit, highlight & sort ascending

0

Hi,

I have a problem I was wondering if you could help me with, I have an example of data below: What I would like to know if all cells in a row are greater than zero for the five columns, eg see row four, how do I change the fill color for every cell for the five columns, for the entire range (ie A1:E9), then sort these ascending by this different color . At the moment I have conditional format to color cells greater than zero.   Any help with this issue would be greatly appreciated.   Regards,   Ron   1 12 -33 100 66 -3 -24 44 -10 -77 -7 36 -1 80 22 22 32 18 44 66 -15 44 34 -65 -62 -19 56 44.8 -102.5 -84 -23 68 55.6 -140 -106 -27 80 66.4 -177.5 -128 -31 92 77.2 -215 -150

exceldata-analysis
MS Office Version: 2016
posted by: Snowie (rep: 2) on Tue Oct 23, 2018 at 11:41 pm
Answer
Discuss

Discussion

This solved the colouring of the cells, how do you do an automatic sort after the conditional formatting is done. Also the filter is showing zero balances, how do you change the formula to only filter values above zero. I tried to change "<0" to ">0", but that did not work.
Snowie (rep: 2) Oct 24, '18 at 9:17 pm
You are right. You ought to be able to change "<0" to ">0". To say "it doesn't work" isn't useful, however. Please post a workbook where I might take a look at what you did. CF is tricky. You may get the desired result by first removing all CFs and then introduce those you want.
About sorting or filtering or showing zeroes please ask separate questions. Avoid starting such questions, however, before all CF problems are resolved so that you can post a workbook with your new question which is free from arried errors.
Variatus (rep: 4889) Oct 25, '18 at 4:49 am
Hi Variatus,
I cleared all conditional formatting, then re-added formatting as =(COUNTIF($A3:$F3,">0")=0) and =(COUNTIF($A4:$F12,">0")=0). Now no row is highlighted orange, rows A6:F6 and A12:F12b should be highlighted.  There is no option to add file  under discuss option, so I cannot add. Please investigate and advise.

Details of sheet are:

-2.49 3.40 12.76 914.81 380.70 149.09
0.00 0.00 79.01 271.79 190.00 705.56
-1.27 9.09 36.84 225.00 178.57 2,900.00
101,283.56 97.56 350.00 211.54 406.25 50.00
-2.44 9.59 35.59 201.89 220.00 4,344.44
-1.56 0.18 43.50 169.47 224.56 661.03
-0.22 -0.22 27.15 141.58 108.64 88.11
2.00 8.51 24.39 131.82 96.15 -10.53
-1.85 4.47 20.85 126.86 181.56 621.82
77.00 11.11 8.33 116.67 71.05 4,233.33
Snowie (rep: 2) Oct 25, '18 at 8:34 pm
Please add the file to the original question. You can do that in edit mode. Up to 3 files can be added. Mind, after you clear all formatting, it is important to observe which cells are selected when you enter the formula. In my answer I detailed very precise steps how I succeeded. If you follow the same steps you should have the same result.
Variatus (rep: 4889) Oct 26, '18 at 3:49 am
Hi Variatus,
I have added this new file to my original question. I still get the same result no cells are highlighted. I cleared all conditional formatting from file, then added =(COUNTIF($A3:$F3,">0")=0), then used Format Painter =(COUNTIF($A4:$F12,">0")=0). Rows A6:F6 and A12:F12 should have been highlighted.
Snowie (rep: 2) Oct 26, '18 at 7:29 pm
Hi Variatus,
I amended the code to =(COUNTIF($B$3:$F$3,"<0")=0), but this does not work, now no row is highlighted, rows A6:F6 and A12:F12 should be highlighted. If it worked for you, please send a copy of your spreadsheet.
Snowie (rep: 2) Nov 1, '18 at 7:37 pm
Hi Vatiatus,
Do you have a formula that will only highlight rows greater than zero in credit (ie 0.01c or greater.
Snowie (rep: 2) Nov 5, '18 at 2:19 am
Hi Variatus,

Your attached file of  181024 is not the solution, as it has both zero and debit balances, in the highlighted row ?

Can you please provide the correct solution, or can you ask someone else to assist with this.
Snowie (rep: 2) Nov 5, '18 at 7:01 pm
Hi Don,
Please take a look at this thread. There seems to be something in it that either I or the OP doesn't see.
Variatus (rep: 4889) Nov 6, '18 at 1:57 am
Add to Discussion

Answers

0
Selected Answer

The solution for your requirement is demonstrated in the attached workbook.

To understand how it works please first consider the basic logic I applied. I wanted a formula which counts cells which meet certain criteria. Using the same COUNIF() function you can count cells containing values >, <, =, <= or >= zero. This function is developed and demonstrated in column H of the Solution worksheet.

The demonstration shows when the count equals zero. This is where you can see if the condition for the count has been adjusted correctly. Modify the data and watch the effect on column H or modify the function to demonstrate a different result, but ignore the CF at this point.

The conditional formatting formula is developed from the function in column H. It isn't identical. The formula used for the CF evaluates the result. "If the function returns 0, then apply the format": =(COUNTIF($B3:$F3,"<=0")=0). You can see the function from column H embedded in the CF formula. So, by embedding a different function in the CF formula you can modify its effect on the colouring. Observe that the range is defined differently in column H and in the CF formula. That is because column H has a purpose slightly different from that in the CF. It isn't a copy-paste job. Rather, it is a logic developed in column H and then applied in the CF formula.

The final task is to apply the CF formula correctly. It is designed (by means of the range definition) to be applicable to all cells in a range (the Applies To range you can access from the CF Manager dialog box). If you make alterations to the range by other available means Excel will tend to create a new rule each time, using the same formula but processing it separately. That will slow down your worksheet and may, when carried to the extreme, crash it.

posted by: Variatus (rep: 4889) on Mon Nov 5, 2018 at 2:35 pm
Discuss

Discussion

Hi Variatus,

Your attached file of  181024 is not the solution, as it has both zero and debit balances, in the highlighted row ?

Can you please provide the correct solution, or can you ask someone else to assist with this.
Snowie (rep: 2) Nov 5, '18 at 7:00 pm
You asked "if all cells in a row are greater than zero for the five columns". The formula provided can identify such rows. If you wish to specify different handling of zero it can be adapted accordingly. You can probably do that yourself but you would have to specify the desired logic precisely.
Variatus (rep: 4889) Nov 6, '18 at 1:55 am
Hi Variatus,
I do not wish this to be never ending request, and clearly this is of no use to other readers. If your fomula works you need to provide me with a spreadsheet that proves that your formula works. As I said in my previous email I need a formula that highlights 5 cells in a row, that have values greater than zero (ie 1 cent or more). Your spreadsheet of 181024, clearly the formula does not work, because:
The highlighted row ie B5:F5:
(A) Is displaying zero values,
(B) Is displaying debit values.
(C) When you enter a credit value conditional highlighting is no longer displayed.

Can you please provide the correct formula/spreadsheet, so this query can be closed. You asked a college Don yesterday, what was his response.
Snowie (rep: 2) Nov 6, '18 at 6:07 pm
I have removed additional information from the workbook attached to my answer. Please focus your attention on the worksheet named Solution.
Variatus (rep: 4889) Nov 7, '18 at 12:34 am
Hi Variatus,
I cannot see your worksheet named Solution ? there is only one called TXL  181107 Highlight Value Greater Zero.xlsx ? If this is the one you meant to show me, how did you get the sheet to not allow zeros, when using the same formula =COUNTIF($B6:$F6,"<=0"), which did not work previously ?
Snowie (rep: 2) Nov 7, '18 at 9:13 pm
Hi Variatus,
I see it is on a seperate tab, but question above still applies. I do not get how you can get the same formula to work now, when it did not work before ? The formula states < 0, but does not accept zeros. Which is what I want, but formula does not make any sense. What did you do to make this work, as I cannot see any difference in formatting ?
Snowie (rep: 2) Nov 7, '18 at 9:18 pm
Hi Variatus,
You have solved my problem, thank you for the comprehensive explanation, and your perserverence. I only have one last question, how does the conditional formatting formula know that cell in column H ( ie H6) is zero ? as there is nothing in the conditional formatting formula that refers to cells in column H , and format if zero?
Snowie (rep: 2) Nov 8, '18 at 4:01 am
There is no link between column H and the CF. As I pointed out, the function in column H is embedded in the CF formula, meaning the function is physically there and a link isn't required for that reason. The connection is in the logic. If the function returns 0 under the correct conditions than its adaption in the CF formula will apply the colouring correctly and v.v. You might say that column H is for testing the function before using it in the formula.
I'm glad we got this one sorted :-)
Variatus (rep: 4889) Nov 8, '18 at 5:11 am
Hi Variatus,
I understand now, after reviewing your previous sheets that did not work. Thanks for all your help with this query. You can put your feet up now V:>)
Snowie (rep: 2) Nov 8, '18 at 9:10 pm
Add to Discussion


Answer the Question

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