Highlighting cells using VBA & Sumif()

[xls]

Find attached file. I have manually highlighted cells which qualifies criterion mentioned using sumif(). Color of the highlighted cells is as same as cells qualified usinf sumif().Is it possible to do using VBA or in any other way.

[acw]

Hi

This has hardcoded ranges but it should get you going.

[VBA]
Sub ccc()
Dim nodupes As New Collection
For Each ce In Range("E80:E88")
nodupes.Add Item:=ce.Offset(0, -1).Interior.ColorIndex, key:=Format(ce.Value, "mmm")
Next ce

For Each ce In Range("C4:C76")
ce.Interior.ColorIndex = nodupes(Format(ce.Offset(0, -2), "mmm"))
ce.Offset(0, 1).Interior.ColorIndex = nodupes(Format(ce.Offset(0, -2), "mmm"))
Next ce

End Sub

[/VBA]


Tony

[Bob Phillips]

What exactly are you wanting? Do you want the code to setup the colours, to calculate the totals nit using SUMIF, or what?

[xls]

What exactly are you wanting? Do you want the code to setup the colours, to calculate the totals nit using SUMIF, or what?

Below there is formula using sum if. I want databse to highlighted according to formulae mentioned.

E.g. In cell sum if is giving data for the period between 01/04/06 and 01/03/06, and highlighted with say yellow color. I want the qualified data as per sum if above also to be highlighted in yellow color.

[xls]

[/vba]

Tony[/quote]

Can u tell me how it works.

[Charlize]

You can use a 'select case' for the months so from 1 to 12 and color the row accordingly with the targetcolumn.[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Dim vmonth As String
If Target.Column = 1 Then
vmonth = CStr(Month(Target.Value))
Select Case vmonth
Case "1"
'yellow
Range("A" & Target.Row & "" & Target.Row).Interior.ColorIndex = 6
Case "2"
'red
Range("A" & Target.Row & "" & Target.Row).Interior.ColorIndex = 3
End Select
End If
End Sub[/VBA]

[acw]

xls

Not sure of the intent of your question. Do you want a breakdown on the logic of the code, or do you want to know how to make the code run and action the data...


Tony

[xls]

xls

Not sure of the intent of your question. Do you want a breakdown on the logic of the code, or do you want to know how to make the code run and action the data...


Tony

I want to know how to make the code run and action the data.

[acw]

Hi

While in your spreadsheet, press ALT F11. This will open the Visual Basic Editor. Go Insert, Module. Copy the code from the board and paste it into this module.

Go back to the spreadsheet, make sure you are on the relevant sheet, press F8, select the macro that you have just inserted, then run.


Tony