VBA modify value based on conditions of another cell

**sk8shorty01** · 07-26-2011, 08:41 AM

Hello again,

I have a macro that my job has requested (because I have written others with help from here!) so I figured I would go back to the place that got me started. Here is the situation:

We are cataloging drawings to create a searchable database. Right now the database is about ready to upload but we have some minor checks we need to do before the upload or the files will kick back. Each drawing has a Discipline (Architectural, Electrical, Civil, Structural, etc.) as well as a sub discipline (Architectural Elements, Architectural Interiors, etc.). Originally they wanted me to write a macro that would remove the Discipline header from the sub discipline (Electrical Power would become Power) so that has already been completed.

Now the new task is to write a macro that checks to make sure that the Discipline that was picked contains that sub discipline, for example you cannot have Architectural as a Discipline and Power as the sub discipline because power is under Electrical. I would need to write something that would read column N (the entire column) and for each row that contains said discipline, it would then look at column BF and make sure that the sub matches up within that row.

For ease if someone just showed me a way to code it for one example I can do it for all the others, I just have no idea how to start it.

So for an example lets say this:

Read Column N, if the word "Architectural" is found then look at column BF for one of these:

Elements
Interiors
Finishes
Demolition
Graphics

If anything else is in column BF then delete (replace with " ") and leave blank.

Hope someone can help as it would, as always with me, make my job much easier.

I actually have to write this code for about 15 disciplines but if I just get a quick example on how to do it I should be able to do the rest without much of an issue.

Thanks in advance!

Mike

**sk8shorty01** · 07-26-2011, 10:20 AM

I just thought of something else I would like to incorporate as well.

On any of the cells in the BF column that get their information deleted, I would like to reformat them so that the fill changes to a red color, doesn't really matter what shade, just red.

Thanks again!

**sk8shorty01** · 07-26-2011, 11:48 AM

My thread title was kind of worded badly so for those of you that look at this again because its different now, sorry about that but I didn't think it explained what I needed very well.

Thanks again.

**tigeravatar** · 07-26-2011, 12:04 PM

sk8shorty01,

Something like this?

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

**sk8shorty01** · 07-26-2011, 12:53 PM

Thank you, and that worked great...

Now they want to add on to this whole macro idea just to complicate things.

Now, instead of just deleting the contents that is in column BF when it does not match up, they would like it to delete it and highlight red, but to take that information that was incorrect in BF and move it to column "W" and add it to the values that are already in there. They would also like it to bold.

So now I just have to figure out how to do the same command but instead of clear contents, I would need it to run like a cut, then paste into another column in bold without removing the contents that already is within that column.

Example below:

Column N reads "architectural"
Column BF reads "power" (which is incorrectly labeled)
cut contents of column BF, highlight BF red
Column W reads "E size sheet"
Value W & "," & Value BF (BOLD) >>> so that it will read "E size sheet, power"

So in all reality I just need to add a copy paste command before the

Cells(NCell.Row, "BF").ClearContents line I assume.

Is this correct, if so is there a simple easy code or should I just attempt to use a copy paste, then go back and remove the contents?

**tigeravatar** · 07-26-2011, 01:02 PM

sk8shorty01,

Updated code:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

**sk8shorty01** · 07-26-2011, 01:18 PM

Thank you and now another quick change. The bolded text they would like to be red as well.

I know it should be written as .font.colorindex = 3

Or at least I assume that, but it errors on me for some reason when I try to combine it with the length of the text that it bolds.

Any thoughts?

And btw thank you so much for the help, its been awesome but slight changes as we go are being brought up.