PDA

View Full Version : Highlighting cells using VBA & Sumif()



xls
02-12-2007, 09:59 PM
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
02-12-2007, 10:13 PM
Hi

This has hardcoded ranges but it should get you going.


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




Tony

Bob Phillips
02-13-2007, 01:58 AM
What exactly are you wanting? Do you want the code to setup the colours, to calculate the totals nit using SUMIF, or what?

xls
02-13-2007, 03:35 AM
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
02-14-2007, 03:23 AM
[/vba]

Tony[/quote]

Can u tell me how it works.

Charlize
02-14-2007, 05:41 AM
You can use a 'select case' for the months so from 1 to 12 and color the row accordingly with the targetcolumn.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 & ":D" & Target.Row).Interior.ColorIndex = 6
Case "2"
'red
Range("A" & Target.Row & ":D" & Target.Row).Interior.ColorIndex = 3
End Select
End If
End Sub

acw
02-14-2007, 03:16 PM
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
02-14-2007, 08:36 PM
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
02-14-2007, 10:13 PM
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