If Function with Text Formatting

[Akash]

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

 

[NickHK]

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]

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

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

 

[NickHK]

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]

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

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

 

[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