Copy a cell value as text over to column B

Hi,

Do you wish the copied cells into column [ B ] to be on the same row where they are found in column [AI], or a contiguous list of cells in column [ B ] not in-line with their original position (row) in column [AI]?

BFN,

fp.

Please try this code:

Option Explicit

    Private Enum Nws                ' WorkSheet navigation
        NwsFirstDataRow = 2         ' change as required
        NwsTarget = 2               ' 2 defines column B
        NwsTest = 35                ' 35 defines column AI
    End Enum
    
Sub CopyRedCells()
    
    Dim R As Long
    
    With ActiveSheet
        For R = NwsFirstDataRow To .UsedRange.SpecialCells(xlCellTypeLastCell).Row
            With .Cells(R, NwsTest)
                If .Interior.ColorIndex = 3 Then
                    .Copy Destination:=.Cells(1, NwsTarget - NwsTest + 1)
                End If
            End With
        Next R
    End With
End Sub

Select allOpen in new window

Just paste all of it to a normal code module (by default 'Module1') in your workbook and save it as macro-enabled workbook with XLSM extension. Run the macro by placing the cursor within the procedure 'CopyRedCells' and press F5 while the sheet you wish to work on is active (last viewed). You can also call the procedure from the Developer tab -> Macros / select 'CopyRedCell' and press Run.
At the top of the code you have three enumerations which you can modify as required: The first data row (above that red cells will not be copied), the target and test columns (currently test column AI and write to column B).
Let me know if you need to adapt this to your requirement in greater detail.

Thanks I will test this.  To answer the question, yes if a cell in IA is formatted red, whatever is in that row needs to mirror in column B.  I did not see any reference to text.  The copied over data needs to be formatted as text. -R-

Regarding your requirement to format the cell 's value in column B as Text, this may produce unexpected results! What is in the cell in column AI? Could it be a formula? If so, would you like the formula to be copied or its result? Could there be a number, either as cell content or cell value (result of a formula)? What is the cell format in column AI?

Code failing on Ln 15:   With .Cells(R, NwsTest)

AI is not a formula, the numbers in column AI are as follows:
084779601000010
044981301000000
077854102000010
084779901000010
016711804000000
068002302000000
043224701R6F158
013990301FT2693
081106301000000

I want to stay away from the E+ 07 type errors.  
In the past we have used things like:  .Range("AI1").Value = .Range("B1").Text

Hope this helps.  -R-

Line 15 can only fail if you have set NwsFirstDataRow or NwsTest to a number smaller than 1. I have tested this code and it worked with me. Consider posting an excerpt from your workbook.
The numbers you are worried about do not pose a problem because my code copies the entire cell, not only its value. If the number displays correctly at its source it will display correctly at its destination.

Not sure Ln 1-8 are even being set, still failing for me.  I have not ever used code that you can set the column, row and other things in the Option Explicit?

Can we remove that and just use the needed values?  If edits are necessary I can just edit the code directly.

Sample file is attached.  Thanks -R-
CopyRedOverSample.xlsx

Works like a charm without any alterations. I have installed the code in the attached workbook.
The variables being declared in as enumerations, separate and distinct from the code (not part of the Option statement), is much better programming practice than writing them into the code. It's rather like asking for the steering wheel in a car to be replaced with a handle bar because that is what you are used to from riding a bicycle. The exact point is that you have all the controls in one place and never need to look at the code.
The enum is now set correctly. If you don't touch it the macro will do its job perfectly.
EXX-130604-Copy-Red-Cells.xlsm

It appears to be error'ing because I am copying it to my Personal.xlsb macro file.  It works fine for me outside of the personal.xlsb.  This is something I will use off and on and will not be contained in a specific workbook.  How do I get this to work inside of the personal.xlsb file? or is there another reason why it is failing for me.

I agree that it is a better programming practice and adds flexibility, now it is understanding it's limitation of use and when it is appropreate to use a when not.

Please advise and thanks. -R-

EXCELent!  Thanks for the code adj, that was it and why it was failing.  It did not run from the personal.xlsb file that I put it in.  I believe it was assumed that it was going to be used in a workbook.  -R-