Converting a Hubitat Devices table to an Excel Spreadsheet

[John_Land]

CAVEAT: The m1_FormatHubitatDeviceInfo macro in this post for cleaning up a copy of the Hubitat device table ONLY works when the device table is initially copied in the Firefox browser; the macro would have to be significantly re-worked if Chrome is used (I did not test any other browsers).

POST REVISED ON 2025-02-15

The Devices table in Hubitat has no apparent way to export itself to an Excel spreadsheet. However, the table can be selected, copied, and pasted into Excel -- with bad results in terms of formatting, handling of hex numbers, and preservation of links. [EDIT: but see post 5 below if you have a platform version preceding version 2.4.0.xxx.] I did come across a suggestion to paste the copied table into Google Sheets first, and then re-copy and paste into Excel -- this results in a somewhat more tractable spreadsheet (2-line items end up in the same cell), but a lot of manual formatting is needed to be presentable.

So I created a macro to ease things along, if you follow these steps exactly:

1. In the Devices page of HE, select the setting "gear" icon, select "Display in list/table format", and select ONLY these items (note that "Type" will still be included in the table):

• "Show device IDs"
• "Show device name" [but not the toggle]
• "Show device network IDs"
• "Show device type"
• "Show device room" [but not the selection button]

2. Back on the Devices page, place your cursor next to the word "Devices" at the very top of the table (above the table header row) and hit Ctr-A to select all.
3. Hit Ctrl-C to copy the entire table.
4. In a blank Google Sheets spreadsheet (free online), click on cell A1 and press Ctrl-V to paste the copied table.
5. Immediately hit Ctrl-C to copy that pasted version of the table.
6. In a blank Excel spreadsheet, click on cell A1 and press Ctrl-V to paste the table copied from Google Sheets.
7. Save the spreadsheet in the macro-enabled format (.xlsm).
8. Copy the VBA macro from below into that spreadsheet and run it (apologies for the weird bolding and text coloration in the code -- that's what the forum software does; however, pasting into VBA fixes all that).

You should get a nicely formatted sortable table with the ID, Label, Name, Type, Room, Device Network ID, and Zigbee ID values in separate columns (note that some devices do not follow the Device Network ID or Zigbee ID formats).

I don't know how to preserve the hyperlinks from the HE table -- they get messed up (missing or generally inaccurate) in the double-pasting process -- so I delete them.

NOTE WELL: The VBA code is mostly reliant on "dead reckoning" with respect to what cells, rows, columns, and cell values it expects to find values. This code works for me, BUT may not work for you.

Note also that Network IDs change from time to time (maybe from re-pairing them?), and that any cell in the DNI field that is longer than 4 characters is moved to the "Zigbee ID" field, as such values seem to be invariant (as opposed to Device Network IDs).

See also my m2_MatchAndHighlightDifferences VBA macro code in a later post for comparing old and new spreadsheets (within the same workbook) that have been processed by this macro. This second macro compares a "record" or initial copy of the device table in Excel to a second "new" copy of the device table and highlights the differences. Also posted below is a macro that copies changed Network IDs from the "new" spreadsheet to the "record" spreadsheet. It works for me, YMMV.

Here's a partial screen shot of the Excel spreadsheet I get for my HE Devices:

VBA CODE:

Sub m1_FormatHubitatDeviceInfo()
    Dim vWorkSheet As Worksheet
    Dim vCell As Range
    Dim vLastRow As Long
    Dim vLines As Variant
    Dim vRow As Long
    Dim vTable As ListObject
    Dim vRange As Range

' set the variable vWorkSheet to the active work sheet
    Set vWorkSheet = ActiveSheet

' delete all objects in the sheet and delete hyperlinks
    For Each vShape In ActiveSheet.Shapes
        vShape.Delete
    Next vShape
   ' vWorkSheet.Cells.ClearFormats
    'remove the hyperlinks because they are not all present & accurate after the pasting operation
    vWorkSheet.Hyperlinks.Delete
    
' search for the first cell that contains "Compatible devices"
    Set vCell = vWorkSheet.UsedRange.Find("Compatible devices", LookIn:=xlValues, LookAt:=xlPart)
    If Not vCell Is Nothing Then
        ' delete all rows from the found cell upwards
        vWorkSheet.Rows("1:" & vCell.Row).Delete
    End If

' some special formating of certain columns as "TEXT" to prevent Excel from treating hex addresses like 0E65 as scientific notation numbers
    Columns("B:G").Select
    Selection.NumberFormat = "@"
   
' remove leading and trailing spaces
    For Each vCell In vWorkSheet.UsedRange
        If Not IsEmpty(vCell.Value) Then
            vCell.Value = Trim(vCell.Value)
        End If
    Next vCell
   
' delete all blank rows
    ' find the last row with data
    vLastRow = vWorkSheet.Cells(vWorkSheet.Rows.Count, "A").End(xlUp).Row
    
    ' loop from the last row to the first row
    For vRow = vLastRow To 1 Step -1
        ' check if the entire row is blank
        If Application.WorksheetFunction.CountA(vWorkSheet.Rows(vRow)) = 0 Then
            ' delete the blank row
            vWorkSheet.Rows(vRow).Delete
        End If
    Next vRow

' insert a blank row at the top of the sheet
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
   
' add 2 new columns
    Columns("A:A").Insert Shift:=xlToRight ' insert a new column A to receive the ID info
    Columns("C:C").Insert Shift:=xlToRight ' insert a new column C to receive the Name info
    
' some general initial formating
    Columns("A:G").Select
    Selection.ColumnWidth = 100 ' this value may have to be adjusted upward for very long Labels, Names, Types, etc.
    ' justify all cells to the top-left
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Columns.AutoFit
    Selection.Rows.AutoFit

    vWorkSheet.Cells.Borders.LineStyle = xlNone
    
    Columns("A").HorizontalAlignment = xlRight ' make column A right justified
  
' move the device ID number from column B to column A
    ' loop through each cell in column B
    For Each vCell In vWorkSheet.UsedRange.Columns("B").Cells
        ' check if the cell contains a numeric value
        If IsNumeric(vCell.Value) And Not IsEmpty(vCell.Value) Then
            ' copy the value and move it one column left and one row down
            vCell.Offset(1, -1).Value = vCell.Value
            ' clear the original cell after moving the value
            vCell.ClearContents
        End If
    Next vCell

' delete all blank rows
    ' find the last row with data
    vLastRow = vWorkSheet.Cells(vWorkSheet.Rows.Count, "A").End(xlUp).Row
    
    ' loop from the last row to the first row
    For vRow = vLastRow To 1 Step -1
        ' check if the entire row is blank
        If Application.WorksheetFunction.CountA(vWorkSheet.Rows(vRow)) = 0 Then
            ' delete the blank row
            vWorkSheet.Rows(vRow).Delete
        End If
    Next vRow
    
' find odd cases of ID numbers in column A that are BELOW their actual row data
    ' loop through rows
    For Each vCell In vWorkSheet.UsedRange.Columns("A").Cells
        ' check if the cell in column A is blank and the cell below contains a numeric value
        If IsEmpty(vCell.Value) And IsNumeric(vCell.Offset(1, 0).Value) And Not IsEmpty(vCell.Offset(1, 0).Value) Then
            ' move the numeric value to the blank cell
            vCell.Value = vCell.Offset(1, 0).Value
            vCell.Offset(1, 0).ClearContents
        End If

        ' check if the cell in column B is blank and delete it if true
        If IsEmpty(vCell.Offset(0, 1).Value) Then
            vCell.Offset(0, 1).Delete Shift:=xlShiftToLeft
            ' move the contents of column C to column B
            vCell.Offset(0, 1).Value = vCell.Offset(0, 2)
            vCell.Offset(0, 2).ClearContents
        End If
        
        ' if the next row is now empty, delete the row
        If Application.WorksheetFunction.CountA(Rows(vCell.Offset(1, 0).Row)) = 0 Then
            vWorkSheet.Rows(vCell.Offset(1, 0).Row).Delete
        End If
    Next vCell

' process the Label/Name column, splitting rows with 2 line into 2 columns, B and C
    ' loop through each cell in column B
    For Each vCell In vWorkSheet.UsedRange.Columns("B").Cells
        ' check if the cell contains line breaks (i.e., multiple lines)
        If InStr(vCell.Value, Chr(10)) > 0 Then
            ' split the cell content into lines using the line break (Chr(10))
            vLines = Split(vCell.Value, Chr(10))
            
            ' If there are two or more lines, move the second line to column C
            If UBound(vLines) >= 1 Then
                ' move the second line to column C
                vCell.Offset(0, 1).Value = vLines(1) ' This moves the second line to column C
                
                ' remove the second line from the original cell
                vCell.Value = vLines(0) ' Retain only the first line in the original cell
            End If
        End If
    Next vCell

' process the DNI column, splitting rows with 2 line into 2 columns, F and G
    ' loop through each cell in column F
    For Each vCell In vWorkSheet.UsedRange.Columns("F").Cells
        ' check if the cell contains line breaks (i.e., multiple lines)
        If InStr(vCell.Value, Chr(10)) > 0 Then
            ' split the cell content into lines using the line break (Chr(10))
            vLines = Split(vCell.Value, Chr(10))
            
            ' if there are two or more lines, move the second line to the next column (G)
            If UBound(vLines) >= 1 Then
                ' move the second line to the next column (B)
                vCell.Offset(0, 1).Value = vLines(1) ' This moves the second line to column G
                
                ' remove the second line from the original cell
                vCell.Value = vLines(0) ' Retain only the first line in the original cell
            End If
        End If
    Next vCell

' for rows with blank Label and Name values, delete the Label cell and move rest of the row left
    ' loop through each cell in columns B and C
    For Each vCell In vWorkSheet.UsedRange.Columns("B").Cells
      If IsEmpty(vCell.Value) And IsEmpty(vCell.Offset(0, 1).Value) Then
           vCell.Delete Shift:=xlToLeft
      End If
    Next vCell

' for each Name in column C that is blank, copy the Label from column B
    ' loop through each cell in column C
    For Each vCell In vWorkSheet.UsedRange.Columns("C").Cells
        ' check if the cell in column C is blank
        If IsEmpty(vCell.Value) Then
            ' copy the value from column B
            vCell.Value = vCell.Offset(0, -1).Value
        End If
    Next vCell

' OPTIONAL: move all long (>4 characters) DNI column values to the Zigbee ID colum
    ' loop through each cell in column F
    For Each vCell In vWorkSheet.UsedRange.Columns("F").Cells
        ' check if the cell in column F is longer than 4 characters
        If Len(vCell) > 4 Then
            ' copy the value to column G
            vCell.Offset(0, 1).Value = vCell.Value
            ' insert a hyphen character in the original cell in column F
            vCell.Value = "-"
        End If
    Next vCell

' delete all blank rows
    ' find the last row with data
    vLastRow = vWorkSheet.Cells(vWorkSheet.Rows.Count, "A").End(xlUp).Row
    
    ' loop from the last row to the first row
    For vRow = vLastRow To 1 Step -1
        ' check if the entire row is blank
        If Application.WorksheetFunction.CountA(vWorkSheet.Rows(vRow)) = 0 Then
            ' delete the blank row
            vWorkSheet.Rows(vRow).Delete
        End If
    Next vRow
    
' insert a header row
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "ID"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Label"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Name"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Type"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Room"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Net ID"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Zigbee ID"
    
' OPTIONAL: apply a table style
    ' define the data range (modify as needed)
        ' find the last row with data
    vLastRow = vWorkSheet.Cells(vWorkSheet.Rows.Count, "A").End(xlUp).Row
    Set vRange = vWorkSheet.Range("A1:G" & vLastRow)
    
    ' ensure there is data in the range
    If vRange.Rows.Count = 1 And vRange.Cells(1, 1).Value = "" Then Exit Sub
    
    ' check if a table already exists in the range
    On Error Resume Next
    Set vTable = vWorkSheet.ListObjects(1) ' assuming there's only one table in the sheet
    On Error GoTo 0
    
    ' if no table exists, create one
    If vTable Is Nothing Then
        Set vTable = vWorkSheet.ListObjects.Add(xlSrcRange, vRange, , xlYes) ' xlYes assumes headers exist
    End If
    
    ' apply a table style (modify the style name as needed)
    vTable.TableStyle = "TableStyleLight7"

' some final formatting
    Columns("A:G").Select
    Selection.ClearFormats
    
    With Selection.Font
        .Name = "Calibri"
        .Size = 11
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Underline = xlUnderlineStyleNone
        .Italic = False
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .ThemeFont = xlThemeFontMinor
    End With
    
    Selection.Columns.AutoFit
    Selection.Rows.AutoFit

    ' color the header row yellow and bold the text
    Range("A1:G1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Font.Bold = True

    ' freeze the top row
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
        .FreezePanes = True
    End With
    
' position the cursor at the head of the Label column and turn on the AutoFilter function
    Range("B1").Select
    Selection.AutoFilter

End Sub

2025-02-15: EDITED post to provide the most recent VBA macro and examples.

[sburke781]

When I first read your post I was going to suggest you talk to @thebearmay but it seems you worked out a solution to this yourself.... Nice work.... You seem like similarly motivated dev's....

[John_Land]

Original post deleted. See post #1 for the most recent VBA code and usage explanation.

[John_Land]

Once you have a nice Excel spreadsheet by using the macro in my original post, how to keep it up to date as devices are added or deleted?

Answer: Run the process above but save the new data to a 2nd sheet (e.g., "New Devices") in the same Excel workbook (i.e., spreadsheet file) as your "old" formatted sheet (e.g., "Hubitat Devices"), then run the m2_MatchAndHighlightDifferences VBA macro below.

In addition, you can update changes to the Network ID field in your "old" formatted sheet (e.g., "Hubitat Devices") by running the m3_NetworkIDUpdate macro below.

Notes:

• The "Set vWorkSheet1" and "Set vWorkSheet2" commands in both macros MUST include the identical names of your old sheet and new sheet.
• The m2_MatchAndHighlightDifferences macro works by comparing, within both formatted sheets, columns A to G inclusive.
• The m3_NetworkIDUpdate macro only compares the device ID (column A) and Net ID (column F) to determine which devices have a new network ID, then copies the new ID to the old formatted sheet.
• Unique cells in your "old" formatted sheet will be colored light green. This indicates that those device values have been changed. If an "old" entry row does not have a counterpart in the "new" sheet, then the whole row is colored light green.
• Unique device IDs in your "new" formatted sheet will be colored light red (pink) indicating new devices or changed values. If a "new" entry row does not have a counterpart in the "old" sheet, then the whole row is colored light red.
• Every row in both the old and new sheets that have any change is marked in column A (device ID) in orange.
• The latest code version filters the rows by the orange marker color, so that you see a compact version display of all different rows in both the old and new sheets (all of the rows can be displayed by turning filtering OFF). You can then copy & paste just the filtered rows into your "old" formatted sheet (I then delete the contents of the "new" sheet -- but not the sheet itself -- to await a next reconciliation of old vs. new devices).
• There is an "OPTIONAL" section in the VBA code that removes coloring from cells with selected keys words -- read the code to see if you want to keep those sections, otherwise you can comment them out or delete them.

BONUS: The 3rd macro below runs the m1_FormatHubitatDeviceInfo macro in post #1 and then the m2_MatchAndHighlightDifferences macro below.

Sub m2_MatchAndHighlightDifferences()
    Dim vWorkSheet1 As Worksheet
    Dim vWorkSheet2 As Worksheet
    Dim vCell As Range
    Dim vColumn As Integer
    Dim vLastRow1 As Long
    Dim vLastRow2 As Long
    Dim vLastCol1 As Long
    Dim vLastCol2 As Long
    Dim vDiffColor1 As Long
    Dim vDiffColor2 As Long
    Dim vMarkerColor As Long
    Dim vMatchCell As Range
    Dim vSheet1Name As String
    Dim vSheet2Name As String
    Dim vDataRange As Range
    Dim vDifferenceCount1 As Long
    Dim vDifferenceCount2 As Long

    ' set your sheet names (********** MODIFY the sheet names as needed **********)
    vSheet1Name = "Hubitat Devices"
    vSheet2Name = "New Devices"
    
    ' set Worksheets to compare
    Set vWorkSheet1 = ActiveWorkbook.Sheets(vSheet1Name)
    Set vWorkSheet2 = ActiveWorkbook.Sheets(vSheet2Name)
    
    ' define the colors for differences and for a marker color
    vDiffColor1 = RGB(200, 255, 200) ' Light green
    vDiffColor2 = RGB(255, 200, 200) ' Light red
    vMarkerColor = RGB(255, 192, 0)  ' Orange
    
    ' turn OFF any filtering (show all rows) or MANY rows may be tinted
    Application.Goto Worksheets(vSheet1Name).Range("A1"), True
    If vWorkSheet1.AutoFilterMode Then vWorkSheet1.AutoFilterMode = False
    If vWorkSheet1.FilterMode Then vWorkSheet1.AutoFilter.ShowAllData
    
    Application.Goto Worksheets(vSheet2Name).Range("A1"), True
    If vWorkSheet2.AutoFilterMode Then vWorkSheet2.AutoFilterMode = False
    If vWorkSheet2.FilterMode Then vWorkSheet2.AutoFilter.ShowAllData

    ' find the last row in both sheets
    vLastRow1 = vWorkSheet1.Cells(vWorkSheet1.Rows.Count, "A").End(xlUp).Row
    vLastRow2 = vWorkSheet2.Cells(vWorkSheet2.Rows.Count, "A").End(xlUp).Row
    
    ' clear fill color from columns A through G, except for first row
    vWorkSheet1.Range("A2:G" & vLastRow1).Interior.ColorIndex = xlNone
    vWorkSheet2.Range("A2:G" & vLastRow2).Interior.ColorIndex = xlNone

    ' loop through each cell in column A of Sheet1 and color the differences
    For Each vCell In vWorkSheet1.Range("A1:A" & vLastRow1)
         ' find a match in column A of Sheet2
        Set vMatchCell = vWorkSheet2.Columns("A").Find(What:=vCell.Value, LookIn:=xlValues, LookAt:=xlWhole)
        
        vDiffExists = False
        
        ' if an ID match is found, compare values in columns B through G
        If Not vMatchCell Is Nothing Then
            For vColumn = 2 To 7 ' columns B to G
                ' compare values in the same relative row and column
                If vWorkSheet1.Cells(vCell.Row, vColumn).Value <> vWorkSheet2.Cells(vMatchCell.Row, vColumn).Value Then
                    ' highlight the differing cell in Sheet1
                    vWorkSheet1.Cells(vCell.Row, vColumn).Interior.Color = vDiffColor1
                    ' mark the ID cell for columns that have differences
                    vWorkSheet1.Cells(vCell.Row, 1).Interior.Color = vMarkerColor
                End If
            Next vColumn
        Else
        ' highlight the entire row for any row that does not match
            vWorkSheet1.Range(vCell.Address, vCell.Offset(0, 6).Address).Interior.Color = vDiffColor1
            vWorkSheet1.Cells(vCell.Row, 1).Interior.Color = vMarkerColor
        End If
    Next vCell
        
    ' loop through each cell in column A of Sheet2 and color the differences
    For Each vCell In vWorkSheet2.Range("A1:A" & vLastRow2)
        ' find a match in column A of Sheet2
        Set vMatchCell = vWorkSheet1.Columns("A").Find(What:=vCell.Value, LookIn:=xlValues, LookAt:=xlWhole)

        vDiffExists = False
        
        ' if a match is found, compare values in columns B through G
        If Not vMatchCell Is Nothing Then
            For vColumn = 2 To 7 ' columns B to G
                ' compare values in the same relative row and column
                If vWorkSheet2.Cells(vCell.Row, vColumn).Value <> vWorkSheet1.Cells(vMatchCell.Row, vColumn).Value Then
                    ' highlight the differing cell in Sheet2
                    vWorkSheet2.Cells(vCell.Row, vColumn).Interior.Color = vDiffColor2
                    ' mark the ID cell for columns that have differences
                    vWorkSheet2.Cells(vCell.Row, 1).Interior.Color = vMarkerColor
                End If
            Next vColumn
        Else
        ' highlight the entire row for any row that does not match
            vWorkSheet2.Range(vCell.Address, vCell.Offset(0, 6).Address).Interior.Color = vDiffColor2
            vWorkSheet2.Cells(vCell.Row, 1).Interior.Color = vMarkerColor
        End If
    Next vCell
    
' OPTIONAL: clear the fill color of selected cells in both sheets
    
    ' FIRST, clear cells in columns A & B if the phrase "(Schlage Lock)" exists in the Name column (C)
    ' find the last row in column B for both sheets
    vLastRow1 = vWorkSheet1.Cells(vWorkSheet1.Rows.Count, "C").End(xlUp).Row
    vLastRow2 = vWorkSheet2.Cells(vWorkSheet2.Rows.Count, "C").End(xlUp).Row
    
    ' loop through each cell in column B for both sheets
    For Each vCell In vWorkSheet1.Range("C1:C" & vLastRow1)
        ' check if the cell contains "(Schlage Lock)"(case-sensitive)
        If vCell.Value Like "*(Schlage Lock)*" Then
            ' clear the fill color in the Label column (B)
            vCell.Offset(0, -1).Interior.Color = xlNone
            ' clear the fill color in the ID column (A)
            vCell.Offset(0, -2).Interior.Color = xlNone
        End If
    Next vCell
    For Each vCell In vWorkSheet2.Range("C1:C" & vLastRow2)
        ' check if the cell contains "(Schlage Lock)"(case-sensitive)
        If vCell.Value Like "*(Schlage Lock)*" Then
            ' clear the fill color in the Label column (B)
            vCell.Offset(0, -1).Interior.Color = xlNone
            ' clear the fill color in the ID column (A)
            vCell.Offset(0, -2).Interior.Color = xlNone
        End If
    Next vCell
    
    'SECOND, clear the fill color of the cells in columns A-G for all rows in which the Label is "Hubitat"
     ' loop through each cell in column B for both sheets
    For Each vCell In vWorkSheet1.Range("B1:B" & vLastRow1)
        ' check if the cell contains exactly "Hubitat"(case-sensitive)
        If vCell.Value Like "*Hubitat*" Then
            ' clear the fill color for columns A-G
            vWorkSheet1.Range(vWorkSheet1.Cells(vCell.Row, "A"), vWorkSheet1.Cells(vCell.Row, "G")).Interior.Color = xlNone
        End If
    Next vCell
    For Each vCell In vWorkSheet2.Range("B1:B" & vLastRow2)
        ' check if the cell contains exactly "Hubitat"(case-sensitive)
        If vCell.Value Like "*Hubitat*" Then
            ' clear the fill color for columns A-G
            vWorkSheet2.Range(vWorkSheet2.Cells(vCell.Row, "A"), vWorkSheet2.Cells(vCell.Row, "G")).Interior.Color = xlNone
        End If
    Next vCell
    
' Loop through each used cell in Sheet1 and count the tinted cells
    ' Find the last used row in columns B to G
    vLastRow1 = vWorkSheet1.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    vDifferenceCount1 = 0 ' initilize the difference count for Sheet 1
    For Each vCell In vWorkSheet1.Range("B2:G" & vLastRow1).Cells
        ' check if the cell has a background color (not default color -1)
        If vCell.Interior.ColorIndex <> xlNone Then
            vDifferenceCount1 = vDifferenceCount1 + 1
        End If
    Next vCell

' Loop through each used cell in Sheet2 and count the tinted cells
    vLastRow2 = vWorkSheet2.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    vDifferenceCount2 = 0 ' initilize the difference count for Sheet 1
    For Each vCell In vWorkSheet2.Range("B2:G" & vLastRow2).Cells
        ' check if the cell has a background color (not default color -1)
        If vCell.Interior.ColorIndex <> xlNone Then
            vDifferenceCount2 = vDifferenceCount2 + 1
        End If
    Next vCell
    
' OPTIONAL: filter by vMarkerColor color in column A
    ' define the data ranges (assumes column A contains data) and filter by vMarkerColor color
    
    ' for worksheet 1, find the last row with data in column A (or any column with data)
    vLastRow1 = vWorkSheet1.Cells(vWorkSheet1.Rows.Count, "A").End(xlUp).Row
    
    ' for worksheet 1, find the last column with data in the first row (or any row with data)
    vLastCol1 = vWorkSheet1.Cells(1, vWorkSheet1.Columns.Count).End(xlToLeft).Column

    ' define the range with data
    Set vDataRange = vWorkSheet1.Range("A1", vWorkSheet1.Cells(vLastRow1, vLastCol1))
    
    vWorkSheet1.AutoFilterMode = False ' clear any existing filters
    vDataRange.AutoFilter Field:=1, Criteria1:=vMarkerColor, Operator:=xlFilterCellColor ' apply the new filter

    ' for worksheet 2, find the last row with data in column A
    vLastRow2 = vWorkSheet2.Cells(vWorkSheet2.Rows.Count, "A").End(xlUp).Row
    
    ' for worksheet 2, find the last column with data in the first row
    vLastCol2 = vWorkSheet2.Cells(1, vWorkSheet2.Columns.Count).End(xlToLeft).Column

    ' define the range with data
    Set vDataRange = vWorkSheet2.Range("A1", vWorkSheet2.Cells(vLastRow2, vLastCol2))
    
    vWorkSheet2.AutoFilterMode = False ' clear any existing filters
    vDataRange.AutoFilter Field:=1, Criteria1:=vMarkerColor, Operator:=xlFilterCellColor ' apply the new filter
    
' go to cell A1 in both sheets
    Application.Goto Worksheets(vSheet2Name).Range("A1"), True
    Application.Goto Worksheets(vSheet1Name).Range("A1"), True ' end up in the main sheet
        
' display completion message
    MsgBox "Comparison complete!" & vbNewLine & vDifferenceCount1 & " changed or unique cells in " _
    & vSheet1Name & " are tinted green." & vbNewLine & vDifferenceCount2 & " changed or unique cells in " _
    & vSheet2Name & " are tinted red.", vbInformation
End Sub

Sub m3_NetworkIDUpdate()
    Dim vWorkSheet1 As Worksheet
    Dim vWorkSheet2 As Worksheet
    Dim vCell1 As Range
    Dim vCell2 As Range
    Dim vLastRow1 As Long
    Dim vLastRow2 As Long
    Dim vMatchFound As Boolean
    Dim vSheet1Name As String
    Dim vSheet2Name As String
    Dim vCopiedCellCount As Long

    ' set your sheet names (********** MODIFY the sheet names as needed **********)
    vSheet1Name = "Hubitat Devices"
    vSheet2Name = "New Devices"
    
    ' set Worksheets to compare
    Set vWorkSheet1 = ActiveWorkbook.Sheets(vSheet1Name)
    Set vWorkSheet2 = ActiveWorkbook.Sheets(vSheet2Name)

    ' turn OFF any filtering (show all rows) or MANY rows may be tinted
    Application.Goto Worksheets(vSheet1Name).Range("A1"), True
    If vWorkSheet1.AutoFilterMode Then vWorkSheet1.AutoFilterMode = False
    If vWorkSheet1.FilterMode Then vWorkSheet1.AutoFilter.ShowAllData
    
    Application.Goto Worksheets(vSheet2Name).Range("A1"), True
    If vWorkSheet2.AutoFilterMode Then vWorkSheet2.AutoFilterMode = False
    If vWorkSheet2.FilterMode Then vWorkSheet2.AutoFilter.ShowAllData
    
    ' find the last row in both sheets for column G
    vLastRow1 = vWorkSheet1.Cells(vWorkSheet1.Rows.Count, "G").End(xlUp).Row
    vLastRow2 = vWorkSheet2.Cells(vWorkSheet2.Rows.Count, "G").End(xlUp).Row
    
    ' loop through each cell in Sheet1 column G
    vCopiedCellCount = 0
    For Each vCell1 In vWorkSheet1.Range("G1:G" & vLastRow1)
        ' look for a match in Sheet2 column G
        vMatchFound = False
        For Each vCell2 In vWorkSheet2.Range("G1:G" & vLastRow2)
            If vCell1.Value = vCell2.Value Then
                vMatchFound = True
                ' check if column F values differ
                If vWorkSheet1.Cells(vCell1.Row, "F").Value <> vWorkSheet2.Cells(vCell2.Row, "F").Value Then
                    ' copy value from Sheet2 column F to Sheet1 column F
                    vWorkSheet2.Cells(vCell2.Row, "F").Copy
                    vWorkSheet1.Cells(vCell1.Row, "F").PasteSpecial Paste:=xlPasteValues
                    vWorkSheet1.Cells(vCell1.Row, "F").PasteSpecial Paste:=xlPasteFormats
                    vCopiedCellCount = vCopiedCellCount + 1
                End If
                Exit For ' exit the inner loop once a match is found
            End If
        Next vCell2
        
        ' OPTIONAL: Handle case where no match is found (if needed)
        ' if "Not vMatchFound" then
            ' Code for handling unmatched values
        'End If
    Next vCell1
    
    ' go to cell A1
    Application.Goto Worksheets(vSheet1Name).Range("A1"), True
    
    ' display completion message
    MsgBox "Comparison and update completed." & vbNewLine & "Net ID cells copied: " & vCopiedCellCount, vbInformation
End Sub

Sub m0_FormatAndCompareHubitatInfo()
    Call m1_FormatHubitatDeviceInfo
    Call m2_MatchAndHighlightDifferences
End Sub

EDIT 2025-02-15: Improved VBA code posted.

EDIT 2025-01-07: revised VBA macro again to compare ALL columns (A-G) produced by the VBA macro in post 3 and individually highlight cells that differ between the compared spreadsheets.

EDIT 2025-01-04: revised VBA macro to compare columns A, B, and C (Device ID, Label, Name) to highlight changes to those values.

[mebejedi]

[EDIT] This only works for versions prior to 2.4.0.xxx

If you highlight the fields from the bottom right, up to the top left, it will maintain the formatting for you. (This is an old trick I learned awhile ago)

Straight into Excel

Google Sheets (And then turn it into a table)

From Google Sheets into Excel

[John_Land]

Unfortunately, that "bottom up" technique does not work with platform version 2.4.0.xxx. The columns are different than you show in your example, and I for sure want to keep the Device ID (not shown in your example).

Also, I can't select the header row when starting from the last row, last column cell -- doing so de-selects all cells and sorts the table by ID number.

What I see, pasting directly into Excel, is that the ID numbers are stacked in the same column as the Label (Name), and the DNI numbers are in separate stacked cells; example:

The DNI problem is solved by first pasting into a blank Google Sheets spreadsheet and then re-copying and pasting into Excel. My macro then separates the two DNI components (actual DNI and Zigbee ID) into separate columns.

Further, the very first device data row (regardless of which device is there) is not formatted exactly the same as every other row -- the ID number is in the first cell (rather than one above the device Label, as in all other rows) and the rest of the row data is shifted one cell right relative to other rows, like this:

Note: if the first device data row is omitted when selecting rows, this odd first row behavior does not exist; my VBA macro assumes that the first device data row is askew, so don't run the unmodified macro on copied rows that do not include the first device data row.

EDIT 2025-01-07: Actually, I've discovered additional rows that have place the ID number in the first cell (rather than one above the device Label, as in all other rows) . I've revised the VBA code in Post #3 to take 2 passes through the data table and fix both types of Device ID placement.

Hence, the need for the technique I outline above (until HE allows for CSV or XLSX export, or actually formats the Device table more like the Apps table, which is much better behaved for copying and pasting into Excel -- but still needs to go through Google Sheets to preserve links).

[mebejedi]

@John_Land

Unfortunately, that "bottom up" technique does not work with platform version 2.4.0.xxx. The columns are different than you show in your example, and I for sure want to keep the Device ID (not shown in your example).

My bad,,,I wasn't aware that this was changed for 2.4.0.

[John_Land]

No problem -- but I did edit my original post to reference your technique for versions prior to 2.4.0.xxx.