As you said ,i used test(), got 14 as colorindex & used colorIndex as 14 in the macro you provided.
However, still the o/p remains same. it seems the value of 'val' is nothing on debug..i think it is the problem. further, it always going else part..pls check o/p.
Output on ith Column
::0;;::0;;::0;;::0;;
::0;;::0;;::0;;::0;;
::0;;::0;;::0;;::0;;
::0;;::0;;::0;;::0;;
Meanwhile, i tried a different approach..it is producing result which is 80% of my expectations. few small issues still exist. it needs to be fixed. i shared macro below.
O/P:--
A1111::0;; |
B1111::1;; |
C1111::0;; |
D1111::0;; |
A2222::0;; |
B2222::0;; |
C2222::1;; |
D2222::0;; |
A3333::0;; |
B3333::0;; |
C3333::0;; |
D3333::1;; |
A4444::1;; |
B4444::0;; |
C4444::0;; |
D4444::0;; |
<tbody>
</tbody>
Macro:
Sub writetoevv_Ultimate_Test()
Application.ScreenUpdating = False
Dim LastRow As Long, val As Range, scrWS As Worksheet, desWB As Workbook, x As Long: x = 2
Set srcws = ThisWorkbook.Sheets("Sheet1")
Dim rng As Range: Set rng = Application.Range("B1:E4")
Dim cel As Range
For Each cel In rng.Cells
With cel
For Each val In srcws.Range("B" & rng.Row & ":E" & rng.Row)
If cel.Interior.ColorIndex = 14 Then
Cells(x, 9) = cel & "::1;;"
Else
Cells(x, 9) = cel & "::0;;"
End If
Next val
x = x + 1
End With
Next cel
Application.ScreenUpdating = True
End Sub