[MTB_Rob_NC]

Ok 's I need some help.

This is what I have done. I have built a model. On tab 1 is the final output, basically it is an if/then statement.

If true return value from Tab2
If fales return value from Tab3

Now my what I cant figure out is.... If it returns the "true" value to return it in say black font, if the "False" value another color.


Any suggestions?

 

[urbaindk]

Ok 's I need some help.

This is what I have done. I have built a model. On tab 1 is the final output, basically it is an if/then statement.

If true return value from Tab2
If fales return value from Tab3

Now my what I cant figure out is.... If it returns the "true" value to return it in say black font, if the "False" value another color.


Any suggestions?

I have no idea. I have enough trouble with if statements! If excel made any sense which it doesn't there would be some sort of tag you could attach to each statement.

 

[MTB_Rob_NC]

I have no idea. I have enough trouble with if statements! If excel made any sense which it doesn't

uhhh... ok thanks :wonky2:

Excel is actually pretty logical, but it does have a few quirks. This one is bothering me.

 

[binary visions]

You're going to have to do it in a macro. TargetAddress will be your "final output" cell, if I understand what you're trying to do.

Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Address = "$A$1" Then
          If Range("A1").Value = True
               Range("A1").Font.ColorIndex = 3
          End If
          If Range("A1").Value = False
               Range("A1").Font.ColorIndex = 1
          End If
     End If
End Sub

ColorIndex 3 is red, 1 is black... I just knew those, you'll have to experiment with others. That will take your "final output" cell and turn it red if true, and black if false.

The statement is saying, any time the workbook changes, check to see if it's "target.address" that changed. If yes, check the value of A1 and change its color depending on its value. If A1 is not a boolean value, you can use quotes around text values too (e.g. If Range("A1").Value = "yes")

Does that answer your question?

 

[Heidi]

Dammit, I think I figured this out before...something to do with conditional formating under the format menu.

 

[urbaindk]

Here you go this should do it:

http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/850c9375bd6bab1f/c92943de38be8ccf#c92943de38be8ccf

Or this.

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/browse_thread/thread/1ef360efc60104ff/71bf60235660821#71bf60235660821

I just googled "color formatting excel logical" and came up with a few hits. Try it and see.

 

[binary visions]

You can do it with conditional formatting in the Format/Conditional Formatting menu, too, but that will only work to change the color of the specific cell you're checking the value of. The macro I gave you performs the same function, but can actually be applied to other cells if you wanted it to (e.g. if you wanted cell B6 to be red when A1 is true, you could do that).

 

[MTB_Rob_NC]

You're going to have to do it in a macro. TargetAddress will be your "final output" cell, if I understand what you're trying to do.

Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Address = "$A$1" Then
          If Range("A1").Value = True
               Range("A1").Font.ColorIndex = 3
          End If
          If Range("A1").Value = False
               Range("A1").Font.ColorIndex = 1
          End If
     End If
End Sub

ColorIndex 3 is red, 1 is black... I just knew those, you'll have to experiment with others. That will take your "final output" cell and turn it red if true, and black if false.

The statement is saying, any time the workbook changes, check to see if it's "target.address" that changed. If yes, check the value of A1 and change its color depending on its value. If A1 is not a boolean value, you can use quotes around text values too (e.g. If Range("A1").Value = "yes")

Does that answer your question?

That is probably going to do it, unfortunately you would have to dumb it down just a little. I am not very good with the VBA stuff.

 

[MTB_Rob_NC]

You can do it with conditional formatting in the Format/Conditional Formatting menu, too, but that will only work to change the color of the specific cell you're checking the value of. The macro I gave you performs the same function, but can actually be applied to other cells if you wanted it to (e.g. if you wanted cell B6 to be red when A1 is true, you could do that).

Conditional formatting wont work in this case because both the true and false values are similar.

 

[sanjuro]

That is probably going to do it, unfortunately you would have to dumb it down just a little. I am not very good with the VBA stuff.

Then send your spreadsheet to BV with a $25 gift certificate...

A macro would be the only way to do it.

 

[binary visions]

Just go to Tools, select Macro and select Visual Basic Editor. Double click on the worksheet on the left that this macro will apply to. Paste in what I wrote.

Here's what the code means:

Private Sub Worksheet_Change(ByVal Target As Range)  [COLOR="Red"]'WHEN THE WORKSHEET CHANGES, CHECK THE VALUE OF THE CELL THAT CHANGED[/COLOR]
     If Target.Address = "$A$1" Then  [COLOR="Red"]'IF THE CELL THAT CHANGED IS CELL A1, THEN DO THIS[/COLOR]
          If Range("A1").Value = True  [COLOR="red"]'IF THE VALUE OF A1 IS TRUE, THEN DO THIS[/COLOR]
               Range("A1").Font.ColorIndex = 3  [COLOR="red"]'CHANGE THE FONT COLOR TO RED[/COLOR]
          End If  [COLOR="red"]'ENDING IF FOR IF A1 IS TRUE[/COLOR]
          If Range("A1").Value = False  [COLOR="red"]'IF THE VALUE OF A1 IS FALSE, THEN DO THIS[/COLOR]
               Range("A1").Font.ColorIndex = 1  [COLOR="red"]'CHANGE THE FONT COLOR TO BLACK[/COLOR]
          End If  [COLOR="red"]'ENDING IF FOR IF A1 IS FALSE[/COLOR]
     End If  [COLOR="red"]'ENDING IF FOR IF THE CHANGED CELL IS A1[/COLOR]
End Sub  [COLOR="red"]'END SUBROUTINE[/COLOR]

 

[MTB_Rob_NC]

Didnt work. Does it have anything to do with the range I want this to actually apply to?

 

[binary visions]

Didnt work. Does it have anything to do with the range I want this to actually apply to?

I'm not sure what you mean by this?

Do you mean, is "A1" some kind of important item? No, it's a totally arbitrary cell value that I stuck in there for an example. If the cell you want it to apply to is C57, then put C57 in there. If you want to check B26's True/False status and change cell D93's color based on the results, then you can do that, too.

 

[binary visions]

If it's not critical that you finish this today, I'll PM you my email address and I can look at it for you. It should only take me 2 minutes to see what's going on with it.

 

[MTB_Rob_NC]

I'm not sure what you mean by this?

Do you mean, is "A1" some kind of important item? No, it's a totally arbitrary cell value that I stuck in there for an example. If the cell you want it to apply to is C57, then put C57 in there. If you want to check B26's True/False status and change cell D93's color based on the results, then you can do that, too.

I want your script to apply to a certain range of cells, not just A1, or it could be whole sheet since only the important stuff is using an if/then statement.

 

[MTB_Rob_NC]

If it's not critical that you finish this today, I'll PM you my email address and I can look at it for you. It should only take me 2 minutes to see what's going on with it.

No rush. Arent we meeting to ride tomorrow anyway? That Catawba THTB thing was canceled. If this is your skill level, I may have an easy $ job for you we can discuss.

 

[binary visions]

Check your PMs.