Excel VBA-Display Text in a Cell Based on Previous Cell's Data

Question

please bear with me as i am fairly new to VBA. Please see the attached images.

I am looking for code to populate cells on the "Summary" sheet with "Yes" or "No" based on whether the previous 5 cells are blank or not, starting with cell G2. Note that the data in the "Summary" sheet is pulled from the "Raw Data" sheet.

Employee 1 for example: Cell G2 on the "Summary" sheet will display "No" since all of the cells in range 'Raw Data'!B2:F2 are not blank. Same with H2 since all of the cells in 'Raw Data'!C2:G2 are not blank. I2 would display "Yes" because there is a blank cell in the range 'Raw Data'!D2:H2.

This would continue for each row that has data in column A.

Thank you in advance and please let me know if I did not explain it sufficiently, Kevin

Answer 1

Enter the following formula into G2 on your Summary Sheet:

=IF(COUNT('Raw Data'!B2:F2)=5,"No","Yes")

You can then drag-copy this to the right (and thereafter, down) as you need it.

And here is a slight modification to not show "Yes" for empty rows:

=IF('Raw Data'!B2="","",IF(COUNT('Raw Data'!B2:F2)=5,"No","Yes"))

Answer 2

This has been tested, everything now should work correctly, not sure if you still need the answer or not but the Summary tab has some conditional formatting applied to obviously highlight green if it Yes and Red if it is No. IN the Raw Data tab I entered random values but that should not matter unless there is something I am unaware of.

Sub Yes_No()


Dim lastrow As Long
Dim lastcol As Long
Dim wb As Workbook
Dim ws As Worksheet
Dim ws2 As Worksheet


Set wb = ActiveWorkbook
Set ws = wb.Sheets("Summary")
Set ws2 = wb.Sheets("Raw Data")
lastcol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
lastrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

 For i = 2 To lastrow
   For j = 7 To lastcol


  If ws2.Cells(i, j - 1) <> "" And _
     ws2.Cells(i, j - 2) <> "" And _
     ws2.Cells(i, j - 3) <> "" And _
     ws2.Cells(i, j - 4) <> "" And _
     ws2.Cells(i, j - 5) <> "" Then

       ws.Cells(i, j).Value = "Yes"

  Else

       ws.Cells(i, j).Value = "No"


 End If


   Next j
 Next i





End Sub