record # if data in other columns in same row

[KDS14589]

The record data for my worksheet (ShC012) is placed in non-adjacent cells, such as C5, E5, Z5, but in the same row. This placement varies up to Column CC (for now but that can expand), I have 1 record that has the only data in CA53. I wish to have in column B of each row with data to have a record # based on the row number but in the format of 5 0’s and with a leading CO, like data in row 35 will have CO00035 in cell B35. The rows with no data will be blank. Data’s in column A is not to be altered, that data rows vary from A2 to A100. I’ve attached a workbook with no sensitive data that I use for demo’s and the example is on tab ‘Records’

My last FAILED attempt at code was (I hadn’t got to the format part)

Dim ws As Worksheet
 Set ws = ShCO12
Dim row As Long
Dim col As Long
Dim last_row As Long
Dim last_col As Long
last_row = Range("B4").End(xlDown).row
last_col = ws.Cells(3, Columns.Count).End(xlToLeft).COLUMN
For last_row = 4 To Range("B4").End(xlDown).row
For last_col = 3 To ws.Cells(4, Columns.Count).End(xlToLeft).COLUMN
For row = 4 To last_row
 
If ws.Cells(row, last_col).COLUMN <> "" Then
ws.Cells(row, 2).Value = ws.Cells(row, 2).row
End If
Next row