CODEWITHSUNDEEP
excelvba
user12867910
user12867910

Reputation: 43

Pasting the checked items of a Sheet to the colored cells of another sheet in VBA Excel

I have a VBA Excel program of copying the cell values to the colored cells of another sheet when the checkbox present in the next cell of sheet 1 is checked. Now my problem is : 1. When the checkbox of the respective row is checked it copies the value of the above cell.(eg. When 21 no"s checkbox is checked, it copies the value 20) 2. When it reaches the uncolored cell it pastes a value and it moves to the next row.

  Sub executeCheckBoxes()


    Dim src As Worksheet
    Dim tgt As Worksheet
    Dim chkbx As CheckBox
    Dim i As Long

    Set src = ThisWorkbook.Worksheets("Sheet1")
    Set tgt = ThisWorkbook.Worksheets("Sheet2")
    srcLR = src.Cells(src.Rows.Count, 1).End(xlUp).Row
    tgtER = tgt.Cells(tgt.Rows.Count, 1).Row + 1
    i = 4
    j = 1
    For Each chkbx In src.CheckBoxes
        If chkbx.Value = xlOn Then
        tgt.Cells(i, j).Value = _
            src.Cells(chkbx.TopLeftCell.Row, 1).Value
        tgt.Activate
            If tgt.Cells(i, j).Interior.ColorIndex <> 0 Then
            'tgt.Cells(i, j).Select
             Debug.Print chkbx.Name
             j = j + 1

            Else
             i = i + 1
             j = 1
            End If

         End If
      Next chkbx

End Sub

So this is my sheet 2 appearance: SHEET 2

I'm having 1 to 278 numbers along with the individual checkboxes in the "sheet 1".But I wanted them to be copied from left to right and that too it should get arranged within the colored cells depending on the selected cell values.

This is my Sheet 1: SHEET 1

Any help would be really appreciated.

Upvotes: 0

Views: 52

Answers (1)

iansedano
iansedano

Reputation: 6481

Function Filled(MyCell As Range)
If MyCell.Interior.ColorIndex > 0 Then
    Result = 1
Else
    Result = ""
End If
Filled = Result
End Function

This code was taken from a discussion here.

Using this function, you could write a while loop that starts at cell 1,1 which then goes horizontally until it reaches a cell with no color. Then it would go to the next line. If it went to the next line and the cell had no color, then you could exit the while loop and send a message that says "run out of cells" or something like that.

This assumes that the colored cells are all exactly the same color. Also it assumes that there are no breaks in color in the line (i.e. one continuous block of color).

Upvotes: 1

Related Questions