Conditional Formatting Question

[Annpan79]

Hello I am hoping that you are able to help please

I have a worksheet which receives a feed into "Sheet 1". If an item in column "F" of sheet1 does not exist in column "H" of sheet 2 then our team need to add it on to Sheet 2. They then set column "R" of sheet 2 with how many instances there have been of the item in Sheet 1. When the item is closed on Sheet 2 - column R is set to "0"

What i need is a something that checks for the below things;

1) If the item does not exist at all in sheet 2 column H - Highlight the cell in red and send message to say that "new items have been found"
2) If the item in sheet 1 does exist in column H of sheet 2 but column R is set to "0" for that item - Highlight the cell in red and send message to say that "new items have been found"
3)If the item exists in sheet 2 but column R in sheet 2 is not "0" for that item then do nothing.

I have been going round in circles with IFAND statements on conditional formatting (I figured i could do a separate macro for sending the message based on the cell colour)

I have come up with this formula to put in to cell Z (unused on the feed sheet) for point 2, but nothing is changing as I would expect it to '=IF(AND($F2=MainRange,CompRange<1),"add to sheet" ,"Do not Add") '. MainRange is a dynamic named range on column H of the main sheet and COMPRange is a dynamic range on column R in the main sheet.

It is acting very strange in that it is highlighting a bunch of items as "do not add" but in the middle of those it is highlighting an identical item (of the same name) as "Add to sheet". It's also highlighting a whole bunch as #Value . I was going to do some conditional formatting based on these cell entries but if it's not changing the values right then the CF will not work.

Any help would be greatly appreciated as I have a bunch of guys who are not adding things that they should......... which is driving me somewhat potty.

Many thanks in advance

 

[Annpan79]

I had alternative idea re this - which may make the formatting easier.

Is there a way to add "Closed" to the text of the cell in column H when column R is changed to "0".

I could then just conditionally format any new items from the feed that are not in column "H"

 

[Annpan79]

Hello - I'm really struggling with both of the above options - meanwhile the guys filling in the spreadsheets are busy messing up the data.

I have decided that the best option is the below with the following option;

Hello I am hoping that you are able to help please

I have a worksheet which receives a feed into "Sheet 1". If an item in column "F" of sheet1 does not exist in column "H" of sheet 2 then our team need to add it on to Sheet 2. They then set column "R" of sheet 2 with how many instances there have been of the item in Sheet 1. When the item is closed on Sheet 2 - column R is set to "0"

What i need is a something that checks for the below things;

1) If the item does not exist at all in sheet 2 column H - Highlight the cell in red and send message to say that "new items have been found"
2) If the item in sheet 1 does exist in column H of sheet 2 but column R Sheet2 is set to "0" for that item and the 'last seen date' on Sheet 1 is less than 30 days ago - Highlight the cell in red and send message to say that "new items have been found"
3)If the item exists in sheet 2 but column R in sheet 2 is not "0" for that item then do nothing.
4)If the item exists in sheet 2, column R in sheet 2 is "0" for that item and the 'last seen date' on Sheet 1 is greater than less than 30 days ago then do nothing .

I have done this code, but it is just changing all cells to red - which isn't what I expected.

Public Sub ConditionalFormat()
Dim wb As Workbook
Dim qv As Worksheet
Dim va As Worksheet
Dim CurrDate As String
Dim count As Long 'we need to use Long instead of Integer
'Excel has more rows than Integer can handle
Set wb = ThisWorkbook

Set qv = wb.Sheets("Sheet1")
Set va = wb.Sheets("Sheet2")
CurrDate = format(DateAdd("d", -30, CDate(va.Cells(1 + count, 1).Value)), "dd.mm.yyyy")

count = 0
Do While qv.Cells(1 + count, 1).Value <> vbNullString 'do while first cell contains data

If qv.Cells(1 + count, 6).Value = va.Cells(1 + count, 8).Value And _
va.Cells(1 + count, 18).Value = 0 And qv.Cells(1 + count, 4).Value < CurrDate Then 'if Name on Sheet1 sheet is equal to Description in Sheet2, Column is equal to 0 AND the 'last seen date' is less than than 30 days ago

qv.Range("F" & count + 1).Interior.Color = RGB(250, 50, 50)

ElseIf qv.Cells(1 + count, 6).Value = va.Cells(1 + count, 8).Value _
And qv.Cells(1 + count, 4).Value > CurrDate Then qv.Range("F" & count + 1).Interior.Color = 2 'Leave as white


ElseIf qv.Cells(1 + count, 6).Value <> va.Cells(1 + count, 8).Value Then qv.Range("F" & count + 1).Interior.Color = RGB(250, 50, 50) 'Change colour to RED
ElseIf qv.Cells(1 + count, 6).Value = "-" Then qv.Range("F" & count + 1).Interior.Color = 2 'Leave as white



End If


count = count + 1 'next row
Loop
End Sub



Any help would be greatly appreciated

 

[Annpan79]

Hello - I'm really struggling with both of the above options - meanwhile the guys filling in the spreadsheets are busy messing up the data.

I have decided that the best option is the below with the following option;

What i need is a something that checks for the below things;

1) If the item does not exist at all in sheet 2 column H - Highlight the cell in red and send message to say that "new items have been found"
2) If the item in sheet 1 does exist in column H of sheet 2 but column R Sheet2 is set to "0" for that item and the 'last seen date' on Sheet 1 is less than 30 days ago - Highlight the cell in red and send message to say that "new items have been found"
3)If the item exists in sheet 2 but column R in sheet 2 is not "0" for that item then do nothing.
4)If the item exists in sheet 2, column R in sheet 2 is "0" for that item and the 'last seen date' on Sheet 1 is greater than less than 30 days ago then do nothing .

I have done this code, but it is just changing all cells to red - which isn't what I expected.

Public Sub ConditionalFormat()
Dim wb As Workbook
Dim qv As Worksheet
Dim va As Worksheet
Dim CurrDate As String
Dim count As Long 'we need to use Long instead of Integer
'Excel has more rows than Integer can handle
Set wb = ThisWorkbook

Set qv = wb.Sheets("Sheet1")
Set va = wb.Sheets("Sheet2")
CurrDate = format(DateAdd("d", -30, CDate(va.Cells(1 + count, 1).Value)), "dd.mm.yyyy")

count = 0
Do While qv.Cells(1 + count, 1).Value <> vbNullString 'do while first cell contains data

If qv.Cells(1 + count, 6).Value = va.Cells(1 + count, 8).Value And _
va.Cells(1 + count, 18).Value = 0 And qv.Cells(1 + count, 4).Value < CurrDate Then 'if Name on Sheet1 sheet is equal to Description in Sheet2, Column is equal to 0 AND the 'last seen date' is less than than 30 days ago

qv.Range("F" & count + 1).Interior.Color = RGB(250, 50, 50)

ElseIf qv.Cells(1 + count, 6).Value = va.Cells(1 + count, 8).Value _
And qv.Cells(1 + count, 4).Value > CurrDate Then qv.Range("F" & count + 1).Interior.Color = 2 'Leave as white


ElseIf qv.Cells(1 + count, 6).Value <> va.Cells(1 + count, 8).Value Then qv.Range("F" & count + 1).Interior.Color = RGB(250, 50, 50) 'Change colour to RED
ElseIf qv.Cells(1 + count, 6).Value = "-" Then qv.Range("F" & count + 1).Interior.Color = 2 'Leave as white



End If


count = count + 1 'next row
Loop
End Sub



Any help would be greatly appreciated

Can anyone help at all please??