Forum Home
Press F1
 
Thread ID: 21751 2002-07-03 04:16:00 Excel John Robb (825) Press F1
Post ID Timestamp Content User
59379 2002-07-03 04:16:00 Is it possible to add an area of an excel spreadsheet selecting only the numbers that are entered in red?

I want to add the numbers in an area C4:H25 that are entered in red and ignore any other numbers in that area.

I have tried using conditional formatting but can't get that to work.

Any help would be aprreciated

Thanks

john 		John Robb (825)
59380 2002-07-03 04:29:00 Assuming the *red* numbers are negative (as apposed to simply being coloured red), you can use the following function:

=SUMIF(C4:H25,"<0") 		Shroeder (492)
59381 2002-07-03 04:44:00 Thanks for that but unfortunately the numbers are all positive numbers and I want to be able to isolate just specific numbers in the matrix
John 		John Robb (825)
59382 2002-07-03 06:12:00 This is a little crude, but works...

Routine loops through column 1, rows 1 to 10, checks font color and if red, sums to X. Total for X is put in colum 1 row 20.

Private Sub CommandButton1_Click()
X = 0
For I = 1 To 10
If Worksheets("Sheet1").Cells(I, 1).Font.Color = RGB(255, 0, 0) Then
X = X + Worksheets("Sheet1").Cells(I, 1).Value
End If
Next I

Worksheets("Sheet1").Cells(20, 1).Value = X
End Sub 		wuppo (41)
59383 2002-07-03 06:28:00 Slightly tidier...

Private Sub CommandButton1_Click()
X = 0
For I = 1 To 10
With Worksheets("Sheet1").Cells(I, 1)
If .Font.Color = RGB(255, 0, 0) Then
X = X + .Value
End If
End With
Next I

Worksheets("Sheet1").Cells(20, 1).Value = X
End Sub 		wuppo (41)
59384 2002-07-03 06:35:00 Thanks for the help. I think this means that I have to click a button for each column in the sheet? Is this right? If so it is not really going to do the trick. I was hoping there would be a way of isolating the Red numbers with an if statement or sumif statement but that does not seem possible.

Anyway thanks for trying it is really appreciated

Regards

John 		John Robb (825)
59385 2002-07-03 12:23:00 John,
The previous example does use a button to run the code. To have the code run when you make a change to a cell in the worksheet, change it to:

Private Sub Worksheet_Change(ByVal Target As Range)
X = 0
For I = 1 To 10
With Worksheets("Sheet1").Cells(I, 1)
If .Font.Color = RGB(255, 0, 0) Then
X = X + .Value
End If
End With
Next I

Worksheets("Sheet1").Cells(20, 1).Value = X
End Sub 		wuppo (41)
59386 2002-07-03 12:30:00 The following macro will do as required - tested under Excel2000

Sub red_cell_sum()
Dim rng As Range
x = 0
For Each rng In Range("c5:h25")
If rng.Font.ColorIndex = 3 Then
x = x + rng.Value
End If
Next rng
MsgBox x
End Sub 		Russell D (18)
59387 2002-07-03 12:31:00 Wuppo I think he wants to do more than 1 column, its not the actual button thats his problem, as I read his reply. That should be possible though with another loop. godfather (25)
59388 2002-07-03 12:40:00 No real problem - Russell D has a much tidier answer (as long as you don't deviate from the default palate - otherwise red may no longer = colorindex 3 :D) wuppo (41)
1 2