I’m sure that like most people, you know someone who lost their job last year or perhaps the company you work for became very lean with everyone doing multiple jobs, feeling under qualified and over stressed. Now that the New Year celebrations are over and work is getting back to it’s usual routine, what kind of picture does your team paint? Are they energised and ready to succeed, or do they look a little lackluster? ‘ add countif formulas to count the number of green and red valuesĪctiveCell.FormulaR1C1 = “=COUNTIF(RC:RC, 1)”ĪctiveCell.FormulaR1C1 = “=COUNTIF(RC:RC,2)” With (Type:=xlExpression, Formula1:=”=$B1″ & lRed) With (Type:=xlExpression, Formula1:=”=$B1=” & lGreen) ‘ add the conditional formatting based on the values in column C ‘ remove all formatting from columns A and B ‘ add a value of 2 if the cells in column B are green ‘ add a value of 1 if the cells in column B are red ‘ Add a new column B to the data and copy the formatting from column A to column BĬolumns(“B:B”). Add a formula to count the number of cells in column B with a value of 2Īnd voila! Now all my colleague needs to do is update the values in column B to change the colours and she can read at a glance how many red and green lines she has.Add a formula to count the number of cells in column B with a value of 1.Add conditional formatting to show a red or green fill in columns A and B based on the new values in column B.Then remove all formatting from both columns.Repeat the same operation, but check the cells in column B to see if they were green and put a cell value of 2.Then I needed the vba to check the cells in the new column B to see if they were red and if so put a cell value of 1.As an example in the code below, I’ve used column A to column B First I needed to copy the formatting of the coloured column to a new blank one.At this point I turned to vba and it turned out to be relatively easy. Google however merely confirmed my suspicions. Having exhausted my knowledge of excel formulae, I then turned to my trusty friend Google search. So, back to the drawing board so to speak. Apparently the colours rarely changed and the sheet she had sent me was only a sample of the data, in fact in reality the data was several thousand lines long. My next question was then “Why don’t you create a column to manage this data and use conditional formatting?” At this point a look of horror came over my colleague’s face. In fact, the red and green lines weren’t based on the values in the cell, but on other data not managed by the sheet. So my first question to my colleague was “What determines whether a cell is red or green?” I thought that I could write a formula to count for example all of the values above 25 or below 10. As far as I am aware, there is no formula in excel which will count the number of cells that are formatted in a specific way. Unfortunately, it is not quite as simple as it sounds. Instead, she’d actually manually changed the fill colours of the cells and wanted me to write a formula to count how many lines were red and how many were green. However, recently a colleague sent me a spreadsheet that didn’t have any conditional formatting in it. The visual display of colour helps me to quickly analyse data in a way that numbers alone never could. I’m a big fan of conditional formatting in Excel.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |