Ping IP Address with VBA code and return results in Excel

Question

I have some visual basic code (see below) that tests an IP connection in column B (of an excel spreadsheet) and puts whether or not it is connected or un-reachable in column c, I was just wondering if you could help me I would like it to be green if 'connected', and any other result would be red. Also, could this script be run automatically on an hourly or daily basis? Many Thanks, Andy ``` Function GetPingResult(Host) Dim objPing As Object Dim objStatus As Object Dim strResult As String Set objPing = GetObject("winmgmts:{impersonationLevel=impersonate}"). _ ExecQuery("Select * from Win32_PingStatus Where Address = '" & Host & "'") For Each objStatus In objPing Select Case objStatus.StatusCode Case 0: strResult = "Connected" Case 11001: strResult = "Buffer too small" Case 11002: strResult = "Destination net unreachable" Case 11003: strResult = "Destination host unreachable" Case 11004: strResult = "Destination protocol unreachable" Case 11005: strResult = "Destination port unreachable…

Accepted Answer

You don't need code for this. Turn all the cells red, then add conditional formatting to make it green when you want.

Home > Conditional Formatting > New Rule > Use a formula...

```
=C2="Connected"
```

and format to green. If you want to do it in code, you can add some lines in your For Each loop

```
If Result = "Connected" Then
    Cell.Offset(0,1).Font.Color = vbGreen
Else
    Cell.Offset(0,1).Font.Color = vbRed
End If
```

Ping IP Address with VBA code and return results in Excel

Solution

Problem