IF function

[gudencough]

I have a list of numbers in column A. Some are formated in 'red' color and
the others are 'black'. How do i format column B so there is an 'X' next to
the numbers that are 'red' and nothing next to numbers that are 'black.'

Thank you

 

[Dave Peterson]

I would take the opposite approach.

I'd put an X in the adjacent column or leave it blank, then use Data|Validation
to color the cell near it.

 

[Brad]

Two steps

Step one create a custom function - if you haven't created a macro before -
do a search of "custom functions excel" - high level "alt f11" add a module
and insert the following code.

Function GetColor(myCell As Range)
GetColor = myCell.Font.ColorIndex
End Function

Step two add the following
=IF(GetColor(A3)=3,"x","")

 

[gudencough]

Perfect! Thank you.
Do you know of anyway to get column B to automatically update if you format
a number in column A 'red'. Righ now you have to double click on the column B
cell and press enter to update it.

 

[Brad]

Sounds like your calculation is set to manual

The way to change it to automatic is different between xl03 and xl07
in XL07

Click the office button (upper left corner)
Excel options
Formulas
click the top left radio button (should have the word automatic immediately
to its right.

 

[gudencough]

Automatic Calculation is already on. Yet column B does not update when a
number in column A is formated to 'red'. Or vice verca, when i take off the
'red' formating in column A, column B does not update by removing the 'X'

 

[Brad]

There might be a better way however the following code should be inserted

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculatefull
End Sub

However, rather than being in a module it has to be in the Micorsoft Excel
Object (section) make sure you put it in the sheet that you have the
calculations

Also not deleting the cell information doesn't change the color of the font

You might what to alter the formula to be
=if(and(len(a3)<>0,getcolor(a3)=3),"x","")

 

[Dave Peterson]

That's the problem with UDF's that rely on formatting. They don't update when
the formatting changes.

You could use:
But this only insures that the function will recalculate when excel
recalculates. Don't trust the results until you force that recalc.

That's why I'd use the X and base the conditional formatting on that other cell.