For Loop with Condition on the Pattern of a Cell

[georgesj]

Hi all,

New to Macros and VBA.
I want to run a loop macro down a column (column 6) and check if the cell is hashed out (pattern 2) or not.
If it is hashed, I want it to enter "-" in the adjacent cell to the right (column 7) and if it isn't I want to copy the text from the unhashed cell (column 6) to the adjacent cell to the right (column 7).

Thanks in advance for any and all help.

George

Please Login or Register  to view this content.

[AB33]

George,
What is not working?

[georgesj]

Currently, I am getting a 438 run time error.

I have read some tutorials and have lifted most of the code from books so I am really not sure why this is not working.

[AB33]

Okay!
In which line do you see the error? Which line is highlighted in yellow?
I suspect it could be this line

Please Login or Register  to view this content.

[JosephP]

perhaps

Please Login or Register  to view this content.

[georgesj]

Hi thanks AB33 and JosephP; that was the error line and the addition of Interior worked.

Unfortunately the if loop is not correct now and it just outputs the result of the 'else' for all cells. Any thoughts please?

[JosephP]

perhaps the pattern is not #2 (which is the third pattern from the left in the dialog) or it is applied by conditional formatting?

[georgesj]

An example of the code which dictates what the pattern is is below:

Please Login or Register  to view this content.

[JosephP]

please post a workbook

[georgesj]

I can't post the workbook because it has other people's work in it.

However, I have made a replica extract in the attached Test Doc.

Essentially, the LHS column has names that are either unavailable (filled with Hashing Pattern 2) or available (Pattern 0). In the RHS column the names of the individuals should only transfer over if the cell is clear, otherwise I would like to enter a "-" or leave it blank. Currently when the command button is activated every name transfers.

Thanks

Test_Doc.xls

[JosephP]

it seems 2 is not a valid pattern value-you actually get -4125 set so use

Please Login or Register  to view this content.

[georgesj]

How strange... I am still using pattern = 2 to set but must use patter = -4125 to recognise it.

Anyway, thank you kindly for your help on this! Perfectly solved!

[JosephP]

you're welcome :-)

please don't forget to mark the thread solved (click the 'thread tools' link at the top, then 'mark solved')

by the way you can also-and should really-use -4125 to set the pattern