Macros imitating conditional formatting based on specific text within cell

Okay so I totally took the long way on this one. I just realized that you pretty much want to format any cells that contain a number in column B through G with the green background.

Below is the code that should accomplish this for you.

Please Login or Register  to view this content.

To insert this code press Alt+F8 on your keyboard. Clear the macro name box and type ColorGreen in the box. Select the create option. Then in between the Sub ColorGreen() and End Sub copy and paste the above code.

Close out of the Visual Basic Window and then press Alt+F8 again. This time select the Color Green option and then select Run.

Let me know if this works for you!

Thanks!

First of all, thank you so much for your help! This is close to what I'm looking for, but not quite. I think I can be more descriptive though, so I will try.

If each cell is an item, then a green cell represents an item that I have, and a white cell represents an item that I don't have. The characters X represent all numbers 0-9. So if I have a cell which contains 0129XX, that cell as well as the two cells below it (which correspond to 012901 and 012902) should all be colored green. Even though the two cells below it just contain a "."

Row 13 is green because column A dictates it. The cell in Column A Row 13 contains the text "013300". Since the first four numbers "0133" are equivalent to the first four numbers in Columns B-G Row 18 ("0133XX"), then all cells in Row 13 represent items that I have. The formatting is such that occasionally the document will call out individual items such as "013300" in Columns E-G Row 13. Occasionally the document will leave those items out as seen in Columns B-D Row 13... in the latter situation, the document assumes that these items are taken care of by the "0133XX" callout.

Columns D and G of Row 18 are green because although those cells contain no X's, they represent an individual part.

i.e.

013401 = item 013401

0134XX = items 013400, 013401, 013402, 013403, 013404, 013405, 013406, 013407, 013408, 013409
013410, 013421, 013437, 013475, etc...... (all combinations of 0-9 for each X)

Sorry if I'm not understanding correctly but from my understand you wanted to

1. Loop through Column A to find cells that contain ####XX
2. If the cell in column A contains ####XX then
3. For that row highlight all cells that contain numerical values green
4. Fill all cells below it whose first four digits matched that of the found cell in column A until the next cell in column A's value was ####XX

Is this correct?

That's perfectly okay, I'm sure it's me being unclear. Thank you for your patience. I want to:

1. Search range B1-AM1925 for cell containing ####XX (i.e. 0129XX in cell B1)
2. Once that cell is found, store the column information for reference later (i.e. column B)
3. Loop through column A looking for all cells beginning with the same first four numbers as the cell found (i.e. 0129)
4. Store the row information for the cells found (i.e. rows 1, 2, and 3)
5. Fill cells B1, B2, and B3 green based on column and row data found.
6. Continue searching range B1-AM1925 for next cell containing ####XX. (i.e. 0130XX)
7. Repeat loop

Okay, I'm not sure but I think this will accomplish it.

Try the below code and let me know if it works:

Please Login or Register  to view this content.

To insert this code press Alt+F8 on your keyboard. Clear the macro name box and type ColorGreen in the box. Select the create option. Then in between the Sub ColorGreenTest2() and End Sub copy and paste the above code.

Close out of the Visual Basic Window and then press Alt+F8 again. This time select the Color GreenTest2 option and then select Run.

This code was able to get all of the cells with text in them, but didn't affect the ones with just periods (".") such as B1, B2, B3, etc... is there any way to highlight those blank cells as well? If not, then so be it. You have been more than helpful.