Solved

Counter number of red cells

Posted on 2000-03-26
7
316 Views
Last Modified: 2008-10-12
I need to be able to count the number of red(font) cells in a certain row in Excel97...Is this possible in a formula, or is some hefty VBA needed?

Thanks!
0
Comment
Question by:rockies1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 17

Expert Comment

by:calacuccia
ID: 2659010
Hi Rockies1,

You will need VBA to do what you ask for. Here's a little routine which will do what you want. I've made it with an inputbox, in which you have to enter the rownumber to check and the result is displayed in a message box.

Sub CountRedCells()
Dim Count As Integer, RowNumber As Integer
RowNumber = InputBox("Enter the row number to check")
For Each c In ActiveSheet.Rows(RowNumber).Cells
If c.Font.ColorIndex = 3 Then Count = Count + 1
Next c
MsgBox ("You have " & Count & " cells with Red text in row " & RowNumber & " !")
End Sub

If you need to count the number of cells with background set to red, the line If... Then... must be replaced by this line:

If c.Interior.ColorIndex = 3 Then Count = Count + 1

Hope this helps

Calacuccia
0
 
LVL 9

Expert Comment

by:antrat
ID: 2659888
Hi rockies 1

If you prefer to have a worksheet formula to do this you can use this a custom function like below:

 Dim Mycell As Range, Mycount As Integer
Function RedFCount(TheRange As Range)
For Each Mycell In TheRange
  If Mycell.Font.ColorIndex = 3 Then
     Mycount = Mycount + 1
  End If
Next
    RedCount = Mycount
 End Function



To put it in hit Alt+F11 then go to Insert>Module and paste it in, then click the top right X and save.

Now either type =RedFCount(A1:A100) or select the function from the User Defined section of the Function Wizard.

TIP: to make the function available in any Workbook:

1: Tools>Macro>Record New Macro

2: Type RedFCount in the "Macro Name" box and select "Personal Macro Workbook"
from the "Store Macro in:" list and click OK.

3:Click any cell then Tools>Macro>Stop Recording.

4:Window>Unhide>Personal.xls

5:Alt+F11 then Ctrl+R and then open "Modules" under "VBAProject (Personal)" from the Project Explorer, (Window with heading "Project-VBAProject"). Then double click "Module1" and paste in the code over the top of what's there.

6:Hit the top right X and then Window>Hide and save.


Hope that helps

antrat

0
 

Author Comment

by:rockies1
ID: 2660924
The problem with those pieces of code is that the red is from conditional formatting, and the code only sees the original formatting...

Any ideas on how to see the conditional formatting?
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 17

Accepted Solution

by:
calacuccia earned 100 total points
ID: 2662350
Conditional formatting...

This one is getting hard.
Following code (code originally from VBoukhar, slightly modified by me) generates one user-defined function in your spreadsheet, which can be called from the Excel sheet, by using the function wizard and looking for 'GetColored' in the User-Defined category, or simply by using the worksheet formula
=GetColored(Range) where range can be typed as in other formula's or selected with the mouse as in other formula's. The function will count all cells which match the 'Format Condition' so it will not be perfect for you if you have multiple types of conditional formats, of which you don't want to count some. Also, it gives troubles, when your condition are using string comparison...

If that's the case (any of two mentioned possible cuases, please report back and we'll see what can be done)

Example:
=GetColored(A1:B20) will look for all conditionally formatted & matching cells in your range A1:B20


Function GetColored(a As Range) As Integer
Application.Volatile
  Dim c As Range
  Dim i As Integer
  i = 0
  For Each c In a
    If IsCond(c) Then
      i = i + 1
    End If
  Next c
  GetColored = i
End Function

Function IsCond(a As Range) As Boolean
  IsCond = False
  If a.FormatConditions.Count = 0 Then
    Exit Function
  End If
  Select Case a.FormatConditions(1).Operator
    Case xlEqual:
           If a.Value = a.FormatConditions(1).Formula1 Then
           IsCond = True
           Exit Function
         End If
    Case xlGreater:
         If a.Value > a.FormatConditions(1).Formula1 Then
           IsCond = True
           Exit Function
         End If
    Case xlGreaterEqual:
         If a.Value >= a.FormatConditions(1).Formula1 Then
           IsCond = True
           Exit Function
         End If
    Case xlLess:
         If a.Value < a.FormatConditions(1).Formula1 Then
           IsCond = True
           Exit Function
         End If
    Case xlLessEqual:
         If a.Value <= a.FormatConditions(1).Formula1 Then
           IsCond = True
           Exit Function
         End If
    Case xlNotEqual:
         If a.Value <> a.FormatConditions(1).Formula1 Then
           IsCond = True
           Exit Function
         End If
    Case xlBetween:
         If a.Value >= a.FormatConditions(1).Formula1 And a.Value <= a.FormatConditions(1).Formula2 Then
           IsCond = True
           Exit Function
         End If
    Case xlNotBetween:
         If a.Value < a.FormatConditions(1).Formula1 Or a.Value > a.FormatConditions(1).Formula2 Then
           IsCond = True
           Exit Function
         End If
    End Select
End Function


Hope this helps

Calacuccia
0
 

Author Comment

by:rockies1
ID: 2664178
Ugh...Still no luck...

Check out the actual spreadsheet in question <a href="http://handy.gvi.net/~rockies1/excel.zip">here</a>.

The function goes to the case xlEqual, but the IF never evaluates true...

I'm not real familiar with the functions you've used, so I really can't figure it out...
0
 

Author Comment

by:rockies1
ID: 2664180
Adjusted points from 50 to 100
0
 
LVL 9

Expert Comment

by:antrat
ID: 2664322
This is a tough one. I can only manage to count cells that have red font applied if the condition is true, but it counts the ones that dont't meet the condition as well.

antrat
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

689 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question