Counting cells with colour fill, minus the cells with text
Hi,
Ratehr new at this.
Have made a macro with the a function to count cells with different fill colours. the thing I need is to disregard the cells with the given colour that have text fill. Is that possible? Macro and Function below.
MACRO;
Please Login or Register to view this content.
FUNCTION;
=(Countcolour($A$1:$AL$88;$B$43))-1
Any input would be valuable.
Thanks!
Last edited by arlu1201; 04-29-2013 at 05:39 AM.
1. Put the function in a code module and declare it as a public function.
2. Note - it doesn't work with conditional formatting
Elegant Simplicity............. Not Always
Try this..
Please Login or Register to view this content.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Crappy,
Welcome to the forum.
I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Hi,
Thanks for the answers both Sixthsense and AndyLitch. I have worded myself somewhat poorly. I have three colours, and it is only for one of them that I want to disregard the cells with text. The colors are Red, Yellow and Green. Red is Non "Compliant", Green is "Compliant", and Yellow is "Required". It is for the yellow color I would like to, not count, the cells with text in them. So if possible, I think it should be in the Function that one disregard the cells with text, not in the macro, as that applies to the whole spreadsheet?
Please attach a sample workbook with expected result for better understanding
130426 - WW (edited).xlsm
Hi,
Please find the file in question attached. Here I have exported a number of sheets from our manning system (the ones with ships names). I have entered the function on every page counting the green, yellow and red cells. Then I copied all these on a sumamry sheet "Total data" and then trying to make a sheet with graphs "Stats". I you look at one of the ships sheets, eg "Taiko" There is a lot of white columns containing names and so on. These are irrelevant to what I am trying to do here.
My problem is that I need to count the green, red and yellow cells, but the yellow cells with text in them should be counted as green. So for this sheet the count should be; Green (comply); 21, Yellow (required);55 and Red (Non compliant); 2. With my formula i get green; 7, yellow; 69 and red; 2.
Is there any way to make a formula that counts yellow cells with text as green under comply and disregrd the yellow cells with text in the required.
Thanks for your patience.
Try this...
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks