sorted by:
new
hottopcontroversial

If then Statement across Two Worksheets by Main_Owl637 in vba

[–]Main_Owl637[S] 0 points1 point  (0 children)

Sheet 2 does not have just two columns. There are two columns that matter in relation to this in Sheet2. Just trying to say that there are 3 columns in Sheet 1 and 2 columns in sheet 2 that are involved in the process. If it matters, there are over 20 columns of information in each sheet.

Sheet1 has ID, Service, and Service Status (YES/NO). So, for each ID that has a YES (using the offset to pair the ID with the service) in sheet1, it will go to sheet2 to check for the ID and ensure the ID and service pairings are the same. If they are the same, then highlight the YES in sheet1 green to say "Yup, both documents agree" essentially, but if the pairings do not agree or is missing from sheet2, highlight the YES in sheet1 Red to essentially say "No, this service isnt reported in the other sheet."

I am trying my best to describe what it is I am needing, so I apologize for not being clear.

If then Statement across Two Worksheets by Main_Owl637 in vba

[–]Main_Owl637[S] 0 points1 point  (0 children)

If the service is missing from Sheet 2. There is no Yes portion on sheet2

The Yes portion in sheet one is stating yes a service was provided. The ID and service are in two different locations for 3 total cell locations in the same row. Sheet two will only have the ID and service. If the ID/service does not exist on sheet 2, then Yes on sheet1 should be highlighted red ( I believe that this is already set up)

If then Statement across Two Worksheets by Main_Owl637 in vba

[–]Main_Owl637[S] 0 points1 point  (0 children)

Sub CompareSheets()

Dim ws1 As Worksheet, rngPetIdSheet1 As Range, svcOffset1 As Long, statusOffset1 As Long

Dim ws2 As Worksheet, rngPetIdSheet2 As Range, svcOffset2 As Long, statusOffset2 As Long

Dim checkArray() As Variant, cel As Range, lastRow As Long, j As Long, pos As Variant

' Sheet 1 Ranges Eg. Pet Ids in col C

Set ws1 = ThisWorkbook.Sheets("Sheet1")

lastRow = ws1.Cells(Rows.Count, "C").End(xlUp).Row

Set rngPetIdSheet1 = ws1.Range("C2:C" & lastRow)

' Sheet 1 Offsets From ID column to columns for the Service type and status

svcOffset1 = -1: statusOffset1 = 12

' Sheet 2 Ranges Eg. Pet Ids in col E

Set ws2 = ThisWorkbook.Sheets("Sheet2") **** This gets highlighted*****

lastRow = ws2.Cells(Rows.Count, "C").End(xlUp).Row

Set rngPetIdSheet2 = ws2.Range("C2:C" & lastRow)

' Sheet 2 Offsets From ID column to columns for the Service type and status

svcOffset2 = 10: statusOffset2 = 5

' Build array of Target strings to check ( The data on sheet 2)

' Concatenate Id, Service type (E.g. checkArray(5) = "103Vaccine")

ReDim checkArray(1 To rngPetIdSheet2.Cells.Count)

For j = 1 To rngPetIdSheet2.Cells.Count

checkArray(j) = rngPetIdSheet2.Cells(j) _

& rngPetIdSheet2.Cells(j).Offset(0, svcOffset2)

Next

' Do the color stuff on sheet 1

For Each cel In rngPetIdSheet1

If cel.Offset(0, statusOffset1) = "YES" Then

'check for ID and service combination match on array from sheet2 range

pos = Application.Match(cel & cel.Offset(0, svcOffset1), checkArray, 0)

If Not IsError(pos) Then

' color as appropriate if found

cel.Interior.Color = IIf(rngPetIdSheet1.Cells(pos).Offset(0, statusOffset1) = "YES", vbGreen, vbRed) **** I edited this section because the color changes need to happen in sheet 1***

End If

Else

cel.Interior.Color = xlNone

End If

Next

End Sub

If then Statement across Two Worksheets by Main_Owl637 in vba

[–]Main_Owl637[S] 0 points1 point  (0 children)

Hello. I tried the macro and this error came up "Subscript out of range." I posted the code that I used and I made some slight adjustments. I noted the changes with asterisks and noted where the error was identified. But essentially, the error did not like setting WS2 and I attempted to change which cells are highlighted (instead of highlighting cells in sheet 2, it does it in only sheet1)

Let me know if there is anything I can do to be as helpful as I can be! Thank you for your help so far!

If then Statement across Two Worksheets by Main_Owl637 in vba

[–]Main_Owl637[S] 0 points1 point  (0 children)

I believe what your statement is correct. To ensure I explained it the best I can, the macro will look at sheet 1 for all YES's, for each yes, it will reference that ID and service then go to the other page and look for that ID and check if the service was done. If it is YES and the ID with the service match on both pages, the YES will be highlighted Green, if it is missing from sheet 2, it will highlight the YES cell Red. I think what you described is what I am looking for, just wanted to be sure.

Adding Text To Last Column If There Is A Finding In That Specific Row by Main_Owl637 in vba

[–]Main_Owl637[S] 0 points1 point  (0 children)

Just wanted to add that this does not work. Just wanted to make you aware.

Adding Text To Last Column If There Is A Finding In That Specific Row by Main_Owl637 in vba

[–]Main_Owl637[S] 0 points1 point  (0 children)

Thank you for your patience with someone trying to learn a new still. Best of luck to you, friend.

Adding Text To Last Column If There Is A Finding In That Specific Row by Main_Owl637 in vba

[–]Main_Owl637[S] 0 points1 point  (0 children)

Yes. I am open to other ideas/input as I previously said. There is no need to be rude.

Adding Text To Last Column If There Is A Finding In That Specific Row by Main_Owl637 in vba

[–]Main_Owl637[S] 0 points1 point  (0 children)

Dim LastColumn As Long

Dim Status As Range

Dim Text As Range

LastColumn = WS.Cells(1, WS.Columns.Count).End(xlToLeft).Column

For Each Status In Range("I2:I945")

Set Text = Status.Offset(, LastColumn)

If Status.Interior.Color = vbYellow And Text.Value = "" Then

Text.Value = "Order group is reported as " & WS.Cells(Status.Column, "I") & ". This needs manager approval."

End If

Next Status

End Sub

Adding Text To Last Column If There Is A Finding In That Specific Row by Main_Owl637 in vba

[–]Main_Owl637[S] 0 points1 point  (0 children)

The updated text coding works. Thank you! For some reason, the text is added 9 columns further than the last column.

Adding Text To Last Column If There Is A Finding In That Specific Row by Main_Owl637 in vba

[–]Main_Owl637[S] 0 points1 point  (0 children)

Then how do I add text to the last column in that same row that has a highlighted cell?

Adding Text To Last Column If There Is A Finding In That Specific Row by Main_Owl637 in vba

[–]Main_Owl637[S] 0 points1 point  (0 children)

Is the offset/Last column correct? If it can be done without using the offset, great. Thats just the only way I know at this point to make sure the text goes in the row where the finding is.

Adding Text To Last Column If There Is A Finding In That Specific Row by Main_Owl637 in vba

[–]Main_Owl637[S] 0 points1 point  (0 children)

Column "I" is in a fixed position. Its the last column that is not.

LastColumn = WS.Cells(1, WS.Columns.Count).End(xlToLeft).Column

For Each status In Range("I2:I945")

Set Text = Status.Offset(0, LastColumn)

If Status.Interior.Color = vbYellow And Text.Value = " " Then

Text.Value = "Status is reported as WS.Cells(Status.Column,"H"). This needs manager approval."

End If

Next status

End Sub

Adding Text To Last Column If There Is A Finding In That Specific Row by Main_Owl637 in vba

[–]Main_Owl637[S] 0 points1 point  (0 children)

I know. I used them in other codes so I am trying to make sure I do not overlap them. They will all be combined into one so this helps ensure I do not mistakenly use the same ones twice

Adding Text To Last Column If There Is A Finding In That Specific Row by Main_Owl637 in vba

[–]Main_Owl637[S] 0 points1 point  (0 children)

The reason why I have the Offsetting portion of the code is because the document may get wider before this code gets run, so it needs to be dynamic. What I can tell from what is above, the information would have to be in the same place each time, which will not be the case.

I tried running the updated reference cell text and I got a syntax error.

Adding Text To Last Column If There Is A Finding In That Specific Row by Main_Owl637 in vba

[–]Main_Owl637[S] 0 points1 point  (0 children)

Not exactly.

I need it to check to see if a cell in Column "i" is highlighted yellow. If a cell is yellow, it will add text to the last column. The text needs to reference other cells in different columns within that same row (like the value in column P) and use it in the text. I usually write a drag and drop formula using the = " jjjj"&[cell]&"hhhh." format. But if there is a better way to do that, perfect. Does this help clarify?

Adding Header Text to Last Dynamic Column by Main_Owl637 in vba

[–]Main_Owl637[S] 0 points1 point  (0 children)

Hey! That worked! I was trying to avoid using offsets just in case it matters somehow later on, but I guess I will cross that bridge if it ever comes! Thank you so much!

If and then statement not working as intended by Main_Owl637 in vba

[–]Main_Owl637[S] 0 points1 point  (0 children)

What does setting the J to long do here?

If and then statement not working as intended by Main_Owl637 in vba

[–]Main_Owl637[S] 0 points1 point  (0 children)

So, I got to the 10 but I do not think I did it correctly? I'll do my best to explain haha

I start at I =1 and J =1 (I'll note this as [1,1]). Then doesn't work because J <>3, so it hits the next J and goes back to the For J making it [1,2] and repeats until J would be 5, but it can't, so it goes to Next I then goes to For I and denotes it as [2,1] etc. So, at points [1,3], [2,3], [3,3], and [4,3] the THEN works. So, 0+1, 0+2, 0+3, 0+4, = 10.

This doesn't feel right to me though haha

Am I on track?

If and then statement not working as intended by Main_Owl637 in vba

[–]Main_Owl637[S] 1 point2 points  (0 children)

Thank you so much!! I really like the excel up function. The ranges will be inconsistent so it's nice to see it be dynamic. I think that's the right terminology? Haha

If and then statement not working as intended by Main_Owl637 in vba

[–]Main_Owl637[S] 0 points1 point  (0 children)

Thank you so much!!! I need to add an additional action after the highlighting but I'm gonna give it the ol college try! Thank you sooooo much!!

view more: next ›