font color vba question

[pong]

I am trying to build a sub to check if a workbook is opened by calling a function
and tell if the workbook on the next cell
but if the user doesnt enter a bookname, "Please enter a bookname"t would just show on the next cell
The thing is i wanna change color of "please enter a bookname" into red and turn the message into bold
so what should i do in the code ?

Sub ChecktheworkbookOpen()
Dim IsOpen As Boolean
Dim BookName As String
Dim i As Integer
For i = 1 To 10
If Cells(i, 2) = "" Then
Cells(i, 2).Offset(, 1) = "Please enter a bookname"

Else
BookName = Cells(i, 2).Value
IsOpen = Check_workbook_open(BookName)

If IsOpen = True Then
Cells(i, 2).Offset(, 1) = "Opened"
Else
Cells(i, 2).Offset(, 1) = "Not yet opened"
End If
End If
Next

 

[VoG]

Try

With Cells(i, 2).Offset(, 1)
    .Value = "Please enter a bookname"
    With .Font
        .Color = RGB(255, 0, 0)
        .Bold = True
    End With
End With

 

[pong]

I substituted Cells(i, 2).Offset(, 1) = "Please enter a bookname" with your code
it changed the color of the "Please enter a bookname" into red
however, it also changed the color "opened " and "not yet opened " message
is there anyway that i can change only the 'please enter a bookname' color
without affecting other 2 ?

 

[VoG]

Please post the actual code that you used. As I intended the code to be used it should not do that.

 

[pong]

This is the original code that i was using
and the color was not changed

Function Check_workbook_open(ByVal Bk As String) As Boolean
Dim T As Excel.Workbook
Err.Clear
On Error Resume Next
Set T = Application.Workbooks(Bk)
Check_workbook_open = Not T Is Nothing
Err.Clear
On Error GoTo 0

End Function

Sub ChecktheworkbookOpen()
Dim IsOpen As Boolean
Dim BookName As String
Dim i As Integer
For i = 1 To 10
If Cells(i, 2) = "" Then
Cells(i, 2).Offset(, 1).Value = "Please enter a bookname"

Else
BookName = Cells(i, 2).Value
IsOpen = Check_workbook_open(BookName)

If IsOpen = True Then
Cells(i, 2).Offset(, 1) = "Opened"
Else
Cells(i, 2).Offset(, 1) = "Not yet opened"
End If
End If
Next

End Sub

Then , i substituted the coloring code
it worked but it also changed the color of the "opened " and "not yet opened "

Sub ChecktheworkbookOpen()
Dim IsOpen As Boolean
Dim BookName As String
Dim i As Integer
For i = 1 To 10
If Cells(i, 2) = "" Then
With Cells(i, 2).Offset(, 1)
    .Value = "Please enter a bookname"
    With .Font
        .Color = RGB(255, 0, 0)
        .Bold = True
    End With
End With

Else
BookName = Cells(i, 2).Value
IsOpen = Check_workbook_open(BookName)

If IsOpen = True Then
Cells(i, 2).Offset(, 1) = "Opened"
Else
Cells(i, 2).Offset(, 1) = "Not yet opened"
End If
End If
Next

Anyway thank you very much for your help

 

[VoG]

try this

Sub ChecktheworkbookOpen()
Dim IsOpen As Boolean
Dim BookName As String
Dim i As Integer
For i = 1 To 10
    If Cells(i, 2) = "" Then
        Cells(i, 2).Offset(, 1).Value = "Please enter a bookname"
    Else
        BookName = Cells(i, 2).Value
        IsOpen = Check_workbook_open(BookName)
        If IsOpen = True Then
            Cells(i, 2).Offset(, 1) = "Opened"
        Else
            Cells(i, 2).Offset(, 1) = "Not yet opened"
        End If
    End If
    If Cells(i, 2).Value = "Please enter a bookname" Then
        Cells(i, 2).Color = RGB(255, 0, 0)
        Cells(i, 2).Bold = True
    End If
Next i
End Sub