If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
IF function
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 |
#2
|
|||
|
|||
IF function
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. gudencough wrote: 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 |
#3
|
|||
|
|||
IF function
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","") -- Wag more, bark less "gudencough" wrote: 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 |
#4
|
|||
|
|||
IF function
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" wrote: 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","") -- Wag more, bark less "gudencough" wrote: 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 |
#5
|
|||
|
|||
IF function
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. -- Wag more, bark less "Dave Peterson" wrote: 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. gudencough wrote: 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 . |
#6
|
|||
|
|||
IF function
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" wrote: 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. -- Wag more, bark less "Dave Peterson" wrote: 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. gudencough wrote: 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 . |
#7
|
|||
|
|||
IF function
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","") -- Wag more, bark less "gudencough" wrote: 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" wrote: 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. -- Wag more, bark less "Dave Peterson" wrote: 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. gudencough wrote: 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 . |
#8
|
|||
|
|||
IF function
That's the problem with UDF's that rely on formatting. They don't update when
the formatting changes. You could use: Function GetColor(myCell As Range) application.volatile '-- added GetColor = myCell.Font.ColorIndex End Function 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. gudencough wrote: 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" wrote: 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","") -- Wag more, bark less "gudencough" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|