Jza

Ive got an excel formatting problemo.

I've got 3 columns with numbers (in my case its stock totals by location). Each is added to create a total (stock). Someone has used this - and decided that they want some of the items from the three columns - and has highlighted various rows/columns in green. I therefore need to create a new total column that excluded anything in green.

Is there a format / IIF command you can use in a new cell that checks to see if the row is not green, and if not adds itself to the total?

Thanks guys!

Jza

markr1963

Jza

This is very rough but might point you in the right direction. First off, I can't see how it can be done with a formula. I put some numbers in col A then defined a range for that list (called col1). Then I've attached a bit of code shown below to the worksheet_change and worksheet_selectionchange events (not sure if they are both necessary):
Application.EnableEvents = False ' this is here to stop endless loops
Dim r As Range
Dim i As Integer
Dim j As Single
Dim k As Single
Dim l As Single
Set r = Range("col1") ' the range defined on your spreadsheet
j = 0 ' initialise the variables
k = 0
l = 0
For i = 1 To r.Rows.Count
If r.Cells(i, 1).Interior.ColorIndex = 6 Then 'yellow
j = j + r.Cells(i, 1).Value
ElseIf r.Cells(i, 1).Interior.ColorIndex = 5 Then 'blue
k = k + r.Cells(i, 1).Value
ElseIf r.Cells(i, 1).Interior.ColorIndex = 4 Then 'green
l = l + r.Cells(i, 1).Value
End If
Next i
Cells(25, 1).Value = j 'three cells to hold the totals
Cells(26, 1).Value = k
Cells(27, 1).Value = l
Application.EnableEvents = True

If you want to use different colours you'll have to change the ColorIndex values.

HTH

Mark

Buckrogers

Would be simplier to add an additional column next to the green column of data. Then by each item that needs to be excluded from the total, enter a Y in the new column. In the end column where you totals are, have an if statement which deducts x qty if the cell next to it has a Y in it.

(This additional column could be hidden from view if required)

Buck