Solved: Colour Cells

[justdriving]

Hi,

I am looking for your suggestion how I can do it.

I have Sheet1 in attached file where I input data manually.

I want to generate report in Sheet2 as it is appearing there.

Please help me know how I can do it using VBA?

[Bob Phillips]

Try this formula

=IF(SUMPRODUCT(--(Sheet1!$A$1:$A$20=$B3),--(Sheet1!$C$1:$C$20=C$2))=0,"M","")

[justdriving]

Yes, I was reading your article about SUMPRODUCT. Before, I arrive at Final answer, I want to thank you (many) in advance. You are awesome.

I will need your help to determine "M" cells based on these criteria: -

(1) Month = "SEP" or "OCT"
(2) PRODUCT type
(3) PRODUCT ID

[Bob Phillips]

You need different formulae for the separate blocks

=IF(SUMPRODUCT(--(TEXT(Sheet1!$B$1:$B$20,"mmm")=$B$2),--(Sheet1!$A$1:$A$20=$B3),--(Sheet1!$C$1:$C$20=C$2))=0,"M","")

and

=IF(SUMPRODUCT(--(TEXT(Sheet1!$B$1:$B$20,"mmm")=$F$2),--(Sheet1!$A$1:$A$20=$F3),--(Sheet1!$C$1:$C$20=G$2))=0,"M","")

[justdriving]

You need different formulae for the separate blocks
[VBA]
=IF(SUMPRODUCT(--(TEXT(Sheet1!$B$1:$B$20,"mmm")=$B$2),--(Sheet1!$A$1:$A$20=$B3),--(Sheet1!$C$1:$C$20=C$2))=0,"M","")

and

=IF(SUMPRODUCT(--(TEXT(Sheet1!$B$1:$B$20,"mmm")=$F$2),--(Sheet1!$A$1:$A$20=$F3),--(Sheet1!$C$1:$C$20=G$2))=0,"M","")[/vba]

I am new learner to VBA and therefore, I thank for your help. I have other commitments and time is also very less. If I will not disturb you then I will request you to please code these in VBA. It will help me a lot. I leave it on you to decide.

[Bob Phillips]

[vba]

Sub ColourCells()
Dim sh As Worksheet
Dim lastRow As Long
Dim rowProduct As Long
Dim colProduct As Long
Dim colMonth As Long
Dim colType As Long
Dim i As Long

Set sh = Worksheets("Sheet2")
sh.Range("C3").Resize(3, 3).Value = "M"
sh.Range("G3").Resize(3, 3).Value = "M"

With Worksheets("Sheet1")

lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow

If .Cells(i, "C").Value <> "" Then

If Month(.Cells(i, "B").Value) = 9 Then

colMonth = 2
Else

colMonth = 6
End If

colType = Application.Match(.Cells(i, "C").Value, sh.Cells(2, colMonth + 1).Resize(, 3), 0)
rowProduct = Application.Match(.Cells(i, "A").Value, sh.Columns(colMonth), 0)
sh.Cells(rowProduct, colMonth + colType).Value = ""
End If
Next i
End With
End Sub[/vba]

[justdriving]

What should I say ...
It is much appreciated.


Thanks