A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

IF function



 
 
Thread Tools Display Modes
  #1  
Old May 21st, 2010, 04:42 PM posted to microsoft.public.excel.misc
gudencough
external usenet poster
 
Posts: 8
Default 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  
Old May 21st, 2010, 05:03 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default 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  
Old May 21st, 2010, 05:11 PM posted to microsoft.public.excel.misc
Brad
external usenet poster
 
Posts: 943
Default 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  
Old May 21st, 2010, 05:21 PM posted to microsoft.public.excel.misc
gudencough
external usenet poster
 
Posts: 8
Default 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  
Old May 21st, 2010, 06:52 PM posted to microsoft.public.excel.misc
Brad
external usenet poster
 
Posts: 943
Default 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  
Old May 21st, 2010, 07:19 PM posted to microsoft.public.excel.misc
gudencough
external usenet poster
 
Posts: 8
Default 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  
Old May 21st, 2010, 07:43 PM posted to microsoft.public.excel.misc
Brad
external usenet poster
 
Posts: 943
Default 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  
Old May 21st, 2010, 07:51 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:24 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.