# VBA script for conditional copy, format & paste from workbook1 to workbook2

#### writetoevv

##### Board Regular
also, in new workbook , Q11111, Q22222,Q33333,Q4444 needs to be copied on B Coloumn of respective rows . [Pardon me, though i mentioned it in initial post, i missed this part in dropbox excel file. i will update.]

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### writetoevv

##### Board Regular
also, in new workbook , Q11111, Q22222,Q33333,Q4444 needs to be copied on B Coloumn of respective rows . [Pardon me, though i mentioned it in initial post, i missed this part in dropbox excel file. i will update.]
with bit of hands on, copying Q11111, Q22222,Q33333,Q4444 in B Coloumn of new workbook has been implemented & done.

however, ith column of new worksheet issue still exist.
Output on ith Column
::0;;::0;;::0;;::0;;
::0;;::0;;::0;;::0;;
::0;;::0;;::0;;::0;;
::0;;::0;;::0;;::0;;

Thanks & Regards.

Last edited:

#### writetoevv

##### Board Regular
1 obersationi noticed

code always going to else part of if ..else, because "val.Interior.ColorIndex returning -4142" & we are checking whether it is equal to 43.
as it is not matching or equal, it is always going to else part of code.

#### mumps

##### Well-known Member
As I mentioned on Post #4 , you have to make sure that the colorindex of the shade of green you use in your cells matches the colorindex in the code. Run the following macro and change the range (in red) to a cell that is colored in green. This will display the colorindex of the shade of green you are using. Replace the "43" in the code with the displayed colorindex. That should fix the problem.
Code:
``````Sub test()
MsgBox Range([COLOR="#FF0000"]"E2"[/COLOR]).Interior.ColorIndex
End Sub``````

#### writetoevv

##### Board Regular
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

Last edited:

#### mumps

##### Well-known Member

Code:
``````Sub writetoevv()
Application.ScreenUpdating = False
Dim LastRow As Long, rng As Range, val As Range, scrWS As Worksheet, desWB As Workbook, x As Long: x = 2
Set srcws = ThisWorkbook.Sheets("Sheet1")
LastRow = srcws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For Each rng In srcws.Range("A1:A" & LastRow)
Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = rng
For Each val In srcws.Range("B" & rng.Row & ":E" & rng.Row)
If val.Interior.ColorIndex = 14 Then
Cells(x, 9) = Cells(x, 9) & val & "::1;;"
Else
Cells(x, 9) = Cells(x, 9) & val & "::0;;"
End If
Next val
x = x + 1
Next rng
Application.ScreenUpdating = True
End Sub``````

Last edited:

#### mumps

##### Well-known Member
This is what I got.
<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:251px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Q11111</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >A1111::0;;B1111::1;;C1111::0;;D1111::0;;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Q22222</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >A2222::0;;B2222::0;;C2222::1;;D2222::0;;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Q33333</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >A3333::0;;B3333::0;;C3333::0;;D3333::1;;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Q4444</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >A4444::1;;B4444::0;;C4444::0;;D4444::0;;</td></tr></table> <br />Excel Tabellen im Web darstellen - Excel Jeanie Html 4

#### writetoevv

##### Board Regular
yes, that is correct format..appreciates..

donot know why im getting w/o values..i used ur vba script as it is except commenting following line..' LastRow = srcws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row ...instead i used a static value of 5/10..but it didnt worked for me

could you share ur macro enabled excel sheet with me..pls

#### mumps

##### Well-known Member
Click the link in Post #16 for the file. You shouldn't have to change any line in the macro.

Last edited:

1,102,763
Messages
5,488,692
Members
407,652
Latest member
apple405

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...