What to do with multiple projects depending on the same source?

Question

I have a macro that makes a formatting rule of multiple cell areas and must paint the cell yellow if it contains "S" and it works. But I would also like the cell to the right of the cell that contains "S" painted yellow, but only one cell to the right - not the whole row, is that possible? I imagine it's going to take place inside the "WITH statement, but I can not really move on

Sub Makro2()
    Range("D6:E30,G6:H30,J6:K30,M6:N30,P6:Q30").Select
    Range("P6").Activate
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""S"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

Solution 1

Look into svn:externals

Solution 2

We've used svn:externals pointing to shared code in practice for a few years now. We have had some interesting problems with it that you should probably consider before using it though. Here is the structure that we have:

root
+---common
|   +---lib1
|   |   \---trunk
|   |       +---include
|   |       \---src
|   \---lib2
|       \---trunk
|           +---include
|           \---src
+---proj1
|   \---trunk
|       +---include
|       |   \---common
|       \---src
|           \---common
\---proj2
    \---trunk
        +---include
        |   \---common
        \---src
            \---common

The common directories in both include and src in a project contain external definitions that pull in the common libraries:

c:\dev> svn pget -v svn:externals proj1\trunk\src\common
Properties on 'c:\dev\proj1\trunk\src\common':
  svn:externals : lib1 http://.../common/lib1/trunk/src
                  lib2 http://.../common/lib2/trunk/src

The problem that we've run into is multifaceted but related to tagging and branching our source as the projects change throughout time. The externals definition that I showed above has a few pretty severe problems if you want to have reproducible builds:

1. It refers to a dynamic target - trunk.
2. It doesn't refer to an explicit revision.

When you branch using svn copy, the externals are copied verbatim since they are really just properties attached to the object. Some of the other svn commands (commit, checkout, and export) actually interpret the properties. When you tag a project, you really want to preserve the state of the project for all time. This means that you have to "pin" the externals to a specific revision so you need to change the externals definition to explicit refer to the revision that you want (e.g., "lib1 -r42 http://.../common/lib1/trunk/src"). This solves one facet of the problem.

If you have to maintain multiple incompatible branches of the common code, then you have to specify which branch you want explicitly along with (possibly) the revision.

Needless to say, this can be a bit of a headache. Luckily someone out there in Subversion land write the svncopy.pl script that automates some of this mess. We are still (and have been) struggling with some of the difficulties supporting this in a field deployed product with a bunch of shared code and a mandate of three different versions in the field at any time.

If you do go down this route, then be sure to consider how you will maintain the linkages as the projects grow and change. We've found that a little bit of time thinking about a process will go a long way here.

Solution 3

Put all shared files in a separate folder in either one of the projects or in a separate one. Then use externals to reference that folder. Mixing files from different places in the same folder is a bad idea.

Solution 4

Externals, but do be aware of this issue:

Subversion update externals to a date

View more solutions

Comments

[FaneDuru]

Do you want setting a formatting rule to the "P6" cell, or to the discontinue range? Since, the discontinue range has areas of two columns, the column E:E of the first area is already conditional formatted. I mean, being "on cell to the right of D:D... The idea is that a format condition acts only to the cell it belongs. But the same code can be adapted to simultaneously format the next cell to the right. If interested, I can show you how you can accomplish it for a cell. Theoretically, it ca be done for only the first right column of each discontinuous range, but a little more complicated.

[FaneDuru]

In order to know if is the case, if I will find some time, to solve the much more complicated discontinuous range, I would like to know how you need the formatting to behave. For instance if an "S" exists in "D10", but no "S" in "E10" having its own formatted, do you like that "E10" to be also highlighted (yellow interior)?

[jhjorsal]

Sorry, I did not get that explained properly, P6 is just the last active cell when I record the macro and mark areas up. I just inserted a picture in the question for better understanding. As I write, my conditional formatting works for my areas, but I just want the cell to the right of the formatted cells painted as well

[FaneDuru]

I understood that you say, but I did not understand what you mean... I asked a question in my previous comment, regarding the "E10" cel behavior if it does not have an "S" like value, but "D10" has. Should its interior be yellow, or not? Should I ask it again? Isn't it clear in terms of what I meant?

[FaneDuru]

Then, your picture does not show any "S" in columns E, H, K ... Is your discontinue range build mistakenly? Did you missed showing us such a situation? Your request involves that the column I mentioned above to also be conditioned to make yellow interior in case of an "S"...

[FaneDuru]

I am afraid that your question, especially after showing the range Range("D6:E30,G6:H30,J6:K30,M6:N30,P6:Q30") and telling about the next right cell to be yellow, is completely confusing. Looking to the picture appearing later, after I worked based on your initial question, the range should only contain a single column per area...

[FaneDuru]

Still alive? Please, try testing the last code I posted (second edit) and send some feedback. There is another answer, too. Please, also test it and say something...

[jhjorsal]

Sorry for my long response times, I live in a world with small children ;-), First I just want to say that your last response works perfectly :-).

[jhjorsal]

I do not know if it makes sense to answer the questions now, but I do anyway. The F column should only be formatted based on what the D column shows, The question with "S" I do not quite understand "S" is not a column, but the letter I check on (maybe I misunderstand the question, English is not my mother tongue) You have completely right in RANGE ("D6: D30, ...) It was just because in a previous attempt I was able to get it to paint in F if I had it in RANGE. But again 1000 thanks for the help

[FaneDuru]

OK. Now, please check the other answer, too and choose the one you like better. We here, when somebody spends some time and answer our question, tick his code left check box, in order to make it accepted answer. In this way, somebody else searching for a similar issue will know that the code works and it is appreciate like being the best. Our main goal here is to help people asking questions, but also giving a tool to the future searches to easier find what they need...

[FaneDuru]

I understood that "S" was only a string to be placed in a cel, but seeing your range you tried (in code) to conditional formate, it looked obvious that this "S" should be placed in column E, D, J, H etc. That's why I asked abut the "S" in columns E, H, K ... For such a question, the second area range should have two conditions and this is what I did in the code after my first edit.

[VBasic2008]

To finalize, I would appreciate it if you could share what was the desired result. I know that some cell(s) had to contain an s and some cell(s) had to be painted. You could clarify (at least for me) by answering the following 3 questions: If D6 is equal to s what should be painted? If E6 is equal to s what should be painted? And if F6 is equal to s what should then be painted? A possible answer could e.g. be 1. D6, F6; 2. E6; 3. nothing.

[jhjorsal]

Sorry for the confusion with the s, it was bad luck that I accidentally got the S column pressed in my macro. If D contains an "S" then D and E must be painted

[Wim Coenen]

svn:externals supports file linking in svn 1.6. 1.6 RC3 is currently already available. The nightly build of tortoiseSVN is built against svn 1.6.

[CodingWithSpike]

@wcoenen: thanks for the info! looks like we are still on SVN 1.5.5 here... no wonder it wasn't working for me :)

[EpicVoyage]

See this other question for more details: stackoverflow.com/q/662898