Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Function with Text Formatting
Hi,
I dont know is it possible or not, but as per logics and with the help of Macros I think we can do it. A column with 2000 Rows. A1 Akash (Red in Color) A2 Akash (Blue in Color) A3 Akash (Green in Color) A4 Akash (Green in Color) A5 Akash (Pink in Color) A6 Akash (Blue in Color) A7 Akash (Red in Color) Now what i want that if the color of the text in A1 Cell is Red than B1 should display 1 Now what i want that if the color of the text in A2 Cell is Blue than B1 should display 2 Now what i want that if the color of the text in A3 Cell is green than B1 should display 3 Now what i want that if the color of the text in A4 Cell is green than B1 should display 3 Now what i want that if the color of the text in A5 Cell is Blue than B1 should display 2 Now what i want that if the color of the text in A6 Cell is Red than B1 should display 1. I want the serian number as per the text color in the Column A. Awaiting for the reply. Thanks Akash |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Function with Text Formatting
Akash,
Something like this function, entered in column B, with the required ccell in A as an argument. Fill down. Note that a change of text colour in A will NOT update the formulae, as this does not trigger a .Calculate. There is also the .Colorindex property you can use, depending on how your test colours are used/defined. Public Function GetColour(argRange As Range) As Variant Dim TestVal As Variant TestVal = argRange.Font.Color If IsNull(TestVal) = True Then GetColour = "Mixed" Exit Function End If Select Case argRange.Font.Color Case 0 GetColour = "Auto/black" Case vbRed GetColour = 1 Case vbBlue GetColour = 2 Case vbGreen GetColour = 3 Case Else GetColour = "Undefined" End Select End Function NickHK "Akash" wrote in message oups.com... Hi, I dont know is it possible or not, but as per logics and with the help of Macros I think we can do it. A column with 2000 Rows. A1 Akash (Red in Color) A2 Akash (Blue in Color) A3 Akash (Green in Color) A4 Akash (Green in Color) A5 Akash (Pink in Color) A6 Akash (Blue in Color) A7 Akash (Red in Color) Now what i want that if the color of the text in A1 Cell is Red than B1 should display 1 Now what i want that if the color of the text in A2 Cell is Blue than B1 should display 2 Now what i want that if the color of the text in A3 Cell is green than B1 should display 3 Now what i want that if the color of the text in A4 Cell is green than B1 should display 3 Now what i want that if the color of the text in A5 Cell is Blue than B1 should display 2 Now what i want that if the color of the text in A6 Cell is Red than B1 should display 1. I want the serian number as per the text color in the Column A. Awaiting for the reply. Thanks Akash |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Urgent help Required If Function with Text Formatting
On Mar 30, 10:33 am, "NickHK" wrote:
Akash, Something like this function, entered in column B, with the required ccell in A as an argument. Fill down. Note that a change of text colour in A will NOT update the formulae, as this does not trigger a .Calculate. There is also the .Colorindex property you can use, depending on how your test colours are used/defined. Public Function GetColour(argRange As Range) As Variant Dim TestVal As Variant TestVal = argRange.Font.Color If IsNull(TestVal) = True Then GetColour = "Mixed" Exit Function End If Select Case argRange.Font.Color Case 0 GetColour = "Auto/black" Case vbRed GetColour = 1 Case vbBlue GetColour = 2 Case vbGreen GetColour = 3 Case Else GetColour = "Undefined" End Select End Function NickHK "Akash" wrote in message oups.com... Hi, I dont know is it possible or not, but as per logics and with the help of Macros I think we can do it. A column with 2000 Rows. A1 Akash (Red in Color) A2 Akash (Blue in Color) A3 Akash (Green in Color) A4 Akash (Green in Color) A5 Akash (Pink in Color) A6 Akash (Blue in Color) A7 Akash (Red in Color) Now what i want that if the color of the text in A1 Cell is Red than B1 should display 1 Now what i want that if the color of the text in A2 Cell is Blue than B1 should display 2 Now what i want that if the color of the text in A3 Cell is green than B1 should display 3 Now what i want that if the color of the text in A4 Cell is green than B1 should display 3 Now what i want that if the color of the text in A5 Cell is Blue than B1 should display 2 Now what i want that if the color of the text in A6 Cell is Red than B1 should display 1. I want the serian number as per the text color in the Column A. Awaiting for the reply. Thanks Akash Hi, I tried but its not working, Can anyone help me in this regard. i am in Midway..... how to do that I have loads of work as the year end is net day. Can any one help me in this regard. Awaiting for the help in urgency. Thanks Akash |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Urgent help Required If Function with Text Formatting
I can't help if you just say "it's not working". What does that mean ?
The code needs to be in a standard module, not on a sheet/ThisWorkbook module. NickHK "Akash" wrote in message ups.com... On Mar 30, 10:33 am, "NickHK" wrote: Akash, Something like this function, entered in column B, with the required ccell in A as an argument. Fill down. Note that a change of text colour in A will NOT update the formulae, as this does not trigger a .Calculate. There is also the .Colorindex property you can use, depending on how your test colours are used/defined. Public Function GetColour(argRange As Range) As Variant Dim TestVal As Variant TestVal = argRange.Font.Color If IsNull(TestVal) = True Then GetColour = "Mixed" Exit Function End If Select Case argRange.Font.Color Case 0 GetColour = "Auto/black" Case vbRed GetColour = 1 Case vbBlue GetColour = 2 Case vbGreen GetColour = 3 Case Else GetColour = "Undefined" End Select End Function NickHK "Akash" wrote in message oups.com... Hi, I dont know is it possible or not, but as per logics and with the help of Macros I think we can do it. A column with 2000 Rows. A1 Akash (Red in Color) A2 Akash (Blue in Color) A3 Akash (Green in Color) A4 Akash (Green in Color) A5 Akash (Pink in Color) A6 Akash (Blue in Color) A7 Akash (Red in Color) Now what i want that if the color of the text in A1 Cell is Red than B1 should display 1 Now what i want that if the color of the text in A2 Cell is Blue than B1 should display 2 Now what i want that if the color of the text in A3 Cell is green than B1 should display 3 Now what i want that if the color of the text in A4 Cell is green than B1 should display 3 Now what i want that if the color of the text in A5 Cell is Blue than B1 should display 2 Now what i want that if the color of the text in A6 Cell is Red than B1 should display 1. I want the serian number as per the text color in the Column A. Awaiting for the reply. Thanks Akash Hi, I tried but its not working, Can anyone help me in this regard. i am in Midway..... how to do that I have loads of work as the year end is net day. Can any one help me in this regard. Awaiting for the help in urgency. Thanks Akash |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Urgent help Required If Function with Text Formatting
On Mar 30, 11:53 am, "NickHK" wrote:
I can't help if you just say "it's not working". What does that mean ? The code needs to be in a standard module, not on a sheet/ThisWorkbook module. NickHK "Akash" wrote in message ups.com... On Mar 30, 10:33 am, "NickHK" wrote: Akash, Something like this function, entered in column B, with the required ccell in A as an argument. Fill down. Note that a change of text colour in A will NOT update the formulae, as this does not trigger a .Calculate. There is also the .Colorindex property you can use, depending on how your test colours are used/defined. Public Function GetColour(argRange As Range) As Variant Dim TestVal As Variant TestVal = argRange.Font.Color If IsNull(TestVal) = True Then GetColour = "Mixed" Exit Function End If Select Case argRange.Font.Color Case 0 GetColour = "Auto/black" Case vbRed GetColour = 1 Case vbBlue GetColour = 2 Case vbGreen GetColour = 3 Case Else GetColour = "Undefined" End Select End Function NickHK "Akash" wrote in message roups.com... Hi, I dont know is it possible or not, but as per logics and with the help of Macros I think we can do it. A column with 2000 Rows. A1 Akash (Red in Color) A2 Akash (Blue in Color) A3 Akash (Green in Color) A4 Akash (Green in Color) A5 Akash (Pink in Color) A6 Akash (Blue in Color) A7 Akash (Red in Color) Now what i want that if the color of the text in A1 Cell is Red than B1 should display 1 Now what i want that if the color of the text in A2 Cell is Blue than B1 should display 2 Now what i want that if the color of the text in A3 Cell is green than B1 should display 3 Now what i want that if the color of the text in A4 Cell is green than B1 should display 3 Now what i want that if the color of the text in A5 Cell is Blue than B1 should display 2 Now what i want that if the color of the text in A6 Cell is Red than B1 should display 1. I want the serian number as per the text color in the Column A. Awaiting for the reply. Thanks Akash Hi, I tried but its not working, Can anyone help me in this regard. i am in Midway..... how to do that I have loads of work as the year end is net day. Can any one help me in this regard. Awaiting for the help in urgency. Thanks Akash Hi Nick, I am sorry, But i am a layman in regard to programming. thats y i told u that its not working. What should i do in this regard. I copied the code and paste it in the Module1... Its not working. then I copied the code and paste it in this Workbook... then also Its not working. I dont know about standard module. Kinldy help me.... I have loads of work. Right Now i am doing it Manually. Pls help me. Akash |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Urgent help Required If Function with Text Formatting
On Mar 30, 1:35 pm, "Akash" wrote:
On Mar 30, 11:53 am, "NickHK" wrote: I can't help if you just say "it's not working". What does that mean ? The code needs to be in a standard module, not on a sheet/ThisWorkbook module. NickHK "Akash" wrote in message oups.com... On Mar 30, 10:33 am, "NickHK" wrote: Akash, Something like this function, entered in column B, with the required ccell in A as an argument. Fill down. Note that a change of text colour in A will NOT update the formulae, as this does not trigger a .Calculate. There is also the .Colorindex property you can use, depending on how your test colours are used/defined. Public Function GetColour(argRange As Range) As Variant Dim TestVal As Variant TestVal = argRange.Font.Color If IsNull(TestVal) = True Then GetColour = "Mixed" Exit Function End If Select Case argRange.Font.Color Case 0 GetColour = "Auto/black" Case vbRed GetColour = 1 Case vbBlue GetColour = 2 Case vbGreen GetColour = 3 Case Else GetColour = "Undefined" End Select End Function NickHK "Akash" wrote in message roups.com... Hi, I dont know is it possible or not, but as per logics and with the help of Macros I think we can do it. A column with 2000 Rows. A1 Akash (Red in Color) A2 Akash (Blue in Color) A3 Akash (Green in Color) A4 Akash (Green in Color) A5 Akash (Pink in Color) A6 Akash (Blue in Color) A7 Akash (Red in Color) Now what i want that if the color of the text in A1 Cell is Red than B1 should display 1 Now what i want that if the color of the text in A2 Cell is Blue than B1 should display 2 Now what i want that if the color of the text in A3 Cell is green than B1 should display 3 Now what i want that if the color of the text in A4 Cell is green than B1 should display 3 Now what i want that if the color of the text in A5 Cell is Blue than B1 should display 2 Now what i want that if the color of the text in A6 Cell is Red than B1 should display 1. I want the serian number as per the text color in the Column A. Awaiting for the reply. Thanks Akash Hi, I tried but its not working, Can anyone help me in this regard. i am in Midway..... how to do that I have loads of work as the year end is net day. Can any one help me in this regard. Awaiting for the help in urgency. Thanks Akash Hi Nick, I am sorry, But i am a layman in regard to programming. thats y i told u that its not working. What should i do in this regard. I copied the code and paste it in the Module1... Its not working. then I copied the code and paste it in this Workbook... then also Its not working. I dont know about standard module. Kinldy help me.... I have loads of work. Right Now i am doing it Manually. Pls help me. Akash I check Nick, Its working and sorry for the above mail. After i have written u a mail and give a thorough search i find the way I found its a function and i had to writ a formula. Thanks a tonnnn. Thanks u very much. Akash |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using text and formatting sum function in formula | Excel Worksheet Functions | |||
Formatting a Sentence With TEXT Function | Excel Discussion (Misc queries) | |||
Formula Text String: Formatting Text and Numbers? | Excel Discussion (Misc queries) | |||
Conditional Formatting based on text within a cell w/ text AND num | Excel Worksheet Functions | |||
Text Function with Different Formatting for Number | Excel Discussion (Misc queries) |