VBA Select Case or If question

[Pinaceous]

Hi All,

Condition, column H has an interior color of red and has the text "Enter Text" written into it and where the cell next to it in column I is blank to run a message box stating "Column H has a value and Column I does not".

Would I either write a Select case for this condition or an IF statement to run a message box for it?

For example, if I have cell H27 where its interior cell color is red and has its text of "Enter Text" in it and its corresponding cell of column I is I27 is blank it will run the message box.

If in using a select case or an IF statement, how would I code this?

I tried experimenting with

Select Case Sheet1.Range("H:H").Interior.Color

But I need to add an additional criterial being that it has a value in this cell and runs the message box only when its corresponding cell of column I is blank.

Please let me know.

Thank you!
pinaceous

 

[Max1616]

I would use an if statement. Something like this:

Sub Text_Check()

Dim i As Long
Dim lrow As Long

lrow = Cells(Rows.Count, 8).End(xlUp).Row 'Determines last row where data is populated in column H

For i = 2 To lrow 'Loops through rows 2 to the last row in column H
    If Cells(i, 8).Interior.Color = 255 And Cells(i, 8) = "Enter Text" And Cells(i, 9) = "" Then 'If cell Hi is red and has "Enter Text" and Cell Ii is blank, then fire the msgbox
    
        MsgBox "Missing Text in Cell H" & i
    
    End If

Next i


End Sub

 

[Pinaceous]

I would use an if statement. Something like this:

Sub Text_Check()

Dim i As Long
Dim lrow As Long

lrow = Cells(Rows.Count, 8).End(xlUp).Row 'Determines last row where data is populated in column H

For i = 2 To lrow 'Loops through rows 2 to the last row in column H
    If Cells(i, 8).Interior.Color = 255 And Cells(i, 8) = "Enter Text" And Cells(i, 9) = "" Then 'If cell Hi is red and has "Enter Text" and Cell Ii is blank, then fire the msgbox
  
        MsgBox "Missing Text in Cell H" & i
  
    End If

Next i


End Sub

Many thanks Max1616!

That hit the spot!

BTW, can you please explain how it works?

Thanks again!
pinaceous

 

[johnnyL]

@Pinaceous, did you look at the code submitted for you? @Max1616 left comments in the code to explain the intentions.

 

[Pinaceous]

Yeah, I was just wondering about the 8 and 9 but its all good I see his notes and was able to figure it out. Thanks!

 

[johnnyL]

8 & 9 are the column #s. abcdefg H I jklmn, etc