Macro to change text color

[Raza1]

Hi Guys,

My first post

I have written a macro to put certain text if conditions are met but now I want the text to color red when the text is either "Please enter Account." or "Please enter Dept/Restaurant." Below is the macro I have written however this is not working. Gives me the error message "400". Any help is really appreciated. Thank you

Sub Check()


For Each Cell In Range("E9:E22")
If Cell.Value <> "" Then

If (Cell.Offset(0, -1).Value = "") And (Cell.Offset(0, -2).Value = "") Then

Cell.Offset(0, -1).Value = "Please enter Account."
Cell.Offset(0, -2).Value = "Please enter Dept/Restaurant."

ElseIf (Cell.Offset(0, -1).Value = "") Then
Cell.Offset(0, -1).Value = "Please enter Account."

ElseIf (Cell.Offset(0, -2).Value = "") Then
Cell.Offset(0, -2).Value = "Please enter Dept/Restaurant."

End If
End If

Next

For Each Cell In Range("E9:E22")
If Cell.Value <> "" Then

If (Cell.Offset(0, -1).Value = "Please enter Account.") And (Cell.Offset(0, -2).Value = "Please enter Dept/Restaurant.") Then

Cell.Offset(0, -1).Font.ColorIndex = 3
Cell.Offset(0, -2).Font.ColorIndex = 3

End If
End If

Next

MsgBox "Validated. Please check & enter missing data"


End Sub

 

[alansidman]

Gives me the error message "400".

When you run the code and get the above message, click on Debug and tell us what line of code is highlighted.

 

[Raza1]

When you run the code and get the above message, click on Debug and tell us what line of code is highlighted.

Sorry, it doesn't give any option to debug. Only the warning box with 400 on it

 

[alansidman]

Suggest you open the VBE, Click on the Debug item and compile the code. Then tell us if there is a compile issue. Without knowing which line is a problem, cannot offer any help. Also, what is the entire error message.

Also, look at this link

https://forums.techguy.org/threads/solved-vba-error-400-running-an-excel-macro.775340/

 

[Raza1]

Suggest you open the VBE, Click on the Debug item and compile the code. Then tell us if there is a compile issue. Without knowing which line is a problem, cannot offer any help. Also, what is the entire error message.

Also, look at this link

https://forums.techguy.org/threads/solved-vba-error-400-running-an-excel-macro.775340/

Thank you for that. It doesn't highlight any line in the code when I debug however the error message is : Run-time error '1004: Application-defined or object-define error.

 

[alansidman]

I took your code and entered data into column E and ran the code. It ran without error for me. I suspect that your workbook may be corrupted or maybe you have an issue with your computer.

Run a google search on "Excel Error Message 400" The good news is that it is not a coding error but something else.

 

[Raza1]

Thank you for your help. I will look into that.
Cheers