highlight cell if meet condition

[jannahbuang]

how can i insert the content in 'title' column in table 1 to empty cell in table 2 like shown below in vba and highlight the cell with red colour after that?
i want to know how the logic will be. table 1 will be in sheet 1 and table 2 will be in sheet 2.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Process[/TD]
[TD]Title[/TD]
[/TR]
[TR]
[TD]25-Feb-13[/TD]
[TD]Packaging[/TD]
[TD]PE Foam Optimization[/TD]
[/TR]
[TR]
[TD]3-Mar-13[/TD]
[TD]IPRINT[/TD]
[TD]IPRINT Screen/Squeegee Lifetime Extension[/TD]
[/TR]
[TR]
[TD]2-Apr-13[/TD]
[TD]RANTEX[/TD]
[TD]Test1[/TD]
[/TR]
[TR]
[TD]3-Apr-13[/TD]
[TD]SMAT[/TD]
[TD]Test2[/TD]
[/TR]
[TR]
[TD]3-Apr-13[/TD]
[TD]RANTEX[/TD]
[TD]Test3[/TD]
[/TR]
[TR]
[TD]5-Apr-13[/TD]
[TD]PLM[/TD]
[TD]Test4[/TD]
[/TR]
[TR]
[TD]5-Apr-13[/TD]
[TD]BARC[/TD]
[TD]Test5[/TD]
[/TR]
[TR]
[TD]5-Apr-13[/TD]
[TD]PLATING[/TD]
[TD]Test6[/TD]
[/TR]
</tbody>[/TABLE]




[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[/TR]
[TR]
[TD]Packaging[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IPRINT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RANTEX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PLM[/TD]
[TD][/TD]
[TD][/TD]
[TD]Test4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BARC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Test5[/TD]
[/TR]
[TR]
[TD]PLATING[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Test6[/TD]
[/TR]
</tbody>[/TABLE]

 

[ace19852]

Hi and welcome to the board I can offer the an alternative solution which is formula based.

Ok First in Sheet 2 make sure the Jan, Feb, Mar, Apr are date formatted to mmm and are based on actual dates in those cells the have e.g. 01/01/2013, 01/02,2013 etc

Then in Sheet2 Cell B2

=IF(MONTH(INDIRECT("Sheet1!$A$"&MATCH($A2,Sheet1!$B$1:$B$9,0)))=MONTH(B$1),VLOOKUP($A2,Sheet1!$B$2:$C$9,2,0),"")

copy that across to E2 that line is done and unique

Sheet2 Cell B3

=IF(MONTH(INDIRECT("Sheet1!$A$"&MATCH($A4,Sheet1!$B$1:$B$9,0)))=MONTH(B$1),VLOOKUP($A4,Sheet1!$B$2:$C$9,2,0),"")

copy that across to E2 and down

so if your range if bigger just copy down from the bottom line.

As far as Conditional formatting is concerned

you can do that in the menu and the formula would be =B2<>"" for the range $B$2:$E$7

Cheers

 

[ace19852]

Just noticed both formulas are the same so you only need to put the first 1 in cell B2 and copy to range

Cheers

 

[jannahbuang]

it work if use formula, but do you have it in vba form?
thanks

 

[ace19852]

Ok if you put this in Module 1 it should work - everytime you want to refresh the data run the macro

Sub MatchandpaintRed()

Sheets("Sheet1").Select
Range("B1").Select
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
ActiveCell.Offset(1, 1).Select
If IsEmpty(cell) Then
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
End If
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IF(MONTH(INDIRECT(""Sheet1!$A$""&MATCH(RC1,Sheet1!R1C2:R9C2,0)))=MONTH(R1C),VLOOKUP(RC1,Sheet1!R2C2:R9C3,2,0),"""")"
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Activate
Range("B2").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=B2<>"""""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

Cheers

 

[jannahbuang]

can you comment the code? thanks for your help by the way.