COPY A ROW BASED ON FONT COLOR

[geno32080]

Hello Excel community,
I have been searching every where. I'm looking for a a macro that when activated with a command button will search the active sheet column B thru F, rows 2 thru 50. find cells with red uppercase font and copy them. I set up a macro so that when the user types in that cell range and tabs to the next cell it changes that font to uppercase and also changes the font to red. The new macro needs to copy that random row, and then will need to paste it to B6, B7, B8, B9 and b10 on a different sheet in the same work book. Is that even possible? any help will be greatly appreciated. Thank you.
Below is an example, copy B2 (customer name) on the active work sheet and paste to B6 on the other sheet, copy C2 (company name) and paste B7, copy D2( manufacture) and paste to B8, copy E2 (model number)and paste to B9 and lastly copy F2 ( phone number) and paste to B10 on the other sheet. The copy feature needs to search the active sheet columns B thru F and Rows 2 Thru 50.

 

[Joe4]

You are welcome!

I added documentation to my code so hopefully you can follow along and see what it is doing along the way. But if you have any questions about the code, please don't hesitate to ask.

 

[geno32080]

Hi Joe, I cant get this to work, would mind taking a look?

 

[Joe4]

How is it that it was working yesterday and not today? What changed?

If you would like me to take a look, then you will need to upload a file to a file sharing site and provide a link to it here so I can download your file.

 

[geno32080]

The truth is, I was at work and assumed it was going to. Sorry. Once I got home and started the process, it all went downhill. Sending that SOS message yesterday was not on the top of my list. I shared the file on google. Thank you, it's large undertaking.

LARL MAIN MENU.xlsm

drive.google.com

 

[Joe4]

OK, you have a lot going on here, and it isn't that easy to follow along as you have a bunch of hidden stuff.
What button have you attached this macro code to?

I also got an error immediately when I opened your workbook, with your "Workbook_Open" event procedure code:

Private Sub Workbook_Open()

Range("G4").Value = Range("G4").Value + 1
 
End Sub

When you are running this sort of code, and your workbook has multiple worksheets, you should always designate which sheet this code is supposed to run against.
You can do that by either selecting the worksheet in your code before running the code against it, or by specifying the sheet name in your range reference.

 

[geno32080]

There is a lot going on. If your not able to show the tabs and stuff I can do that and resend it. Some of those things are not needed, It's work in progress. I don't get that era code when i open the program locally. Initially that macro was suppose to assign a number to the work order sheet 2 it suppose to be "B4"
The macro you wrote I'm trying to get it to work on the customer information page. Sheet 3 CommandButton1 (select customer). My original thought was from the create work order sheet2. the end user would select customer information, then scroll to the customer name, select that cell, it would high light that row, then they would click on select customer, then click select return to order, (that maybe able to be combined with the select customer button?)and the customer info would be filled in on cells B7:B11. the add customer info on the work order sheet probably will not be needed.

 

[Joe4]

It doesn't appear that you have assigned the new code to the "Select Customer" button.

 

[geno32080]

Really? the button in an active x control button. CommandButton1 Click.

 

[Joe4]

OK, I see that you actually have placed this code in multiple places. I found it in Module6, and that is where I was watching it. I noticed that when you clicked the button, that code was not invoked.
I also see that you have another copy of it in your "CUSTOMER INFORMATIOM" sheet, and that is what is attached to that button. I never use ActiveX controls myself, I just use the Form Controls (they tend to be less problematic - sometimes ActiveX controls invoke virus vulnerability warnings).

I see what the issue is. It looks like the bold/red font is not hard-coded, but rather being set by Conditional Formatting. When set by Conditional Formatting, you cannot use the standard cells properties to check them. You need to check the Display Format.

So this line:

    If (Cells(r1, "A").Font.Color = vbRed) And _
        (Cells(r1, "A").Font.Bold = True) Then

would need to be changed to this:

    If (Cells(r1, "A").DisplayFormat.Font.Color = vbRed) And _
        (Cells(r1, "A").DisplayFormat.Font.Bold = True) Then

 

[geno32080]

Were getting close. It copies , but only B4 from the customer information sheet, and it does paste to the create work order sheet. but it pasted it to B4 also.
No mater what row I enter information on the customer information page, that's the same row it pastes to on the create work order sheet.

So, if we can get the macro to copy columns B C D E F on any row between 4 and 50 that is red font and bold from the customer information sheet, and paste it to B7,B8,B9,B10 AND B11 on the create work order sheet, that would be awesome.
Thanks for all you help. I'm learning new stuff everyday.