Change color base on upper case and lower cas

Closed
Report
-
Posts
10
Registration date
Wednesday September 9, 2009
Status
Member
Last seen
September 8, 2010
-
Hello,

We have lots of data. In the worksheet there are text comes with value. I want to change cell color base on there upper case and lower case. If there is any capital text in worksheet that cell I wanted in lite green color. If there is any lower case text in worksheet that cell I wanted in lite yellow color. Please give me any suggestion.
below is exaple of my sheet.

Thanks In Advanced.


42d 32 49C
42 56Abd 44
4e 6 5
100 100 100

6 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
try this macro

Sub test()
Dim rng As Range, c As Range
Set rng = Range(Range("A1"), Range("A1").End(xlDown))
For Each c In rng
If LCase(Left(c, 1)) = c Then c.Interior.ColorIndex = 6
If UCase(Left(c, 1)) = c Then c.Interior.ColorIndex = 4
Next c
End Sub
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
quote
If I add macro in excel sheet how can I give a shortcut key for this macor.
unquote

go to the spredsheet.
tools(menu bar)-macros-mcros
you will see the name of the macro. select it
on the righthand side click "options"
in the "macro options" window
choose your short cut key
click ok
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
10
Registration date
Wednesday September 9, 2009
Status
Member
Last seen
September 8, 2010
1
Actually first one was right its happpend by mistake by me. sorry for that. I want upper case green and lower case yellow that was right sorry for last wrong massage.

Thanks and regards
sanjay
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
10
Registration date
Wednesday September 9, 2009
Status
Member
Last seen
September 8, 2010
1
Hi,
Thanks for answer. But stiil problem. I have a lots of data in excel file. There are too many alphabets with numbers for example (34d) (24eB). There is hole sheet. I want to change all sectation color at the one time. for small case (34d) yellow and for (24eb) green. If I add macro in excel sheet how can I give a shortcut key for this macor.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
did you try the macro? what probllem you had.
Posts
10
Registration date
Wednesday September 9, 2009
Status
Member
Last seen
September 8, 2010
1
Hi,

If there is first cell (A1) "55j" and below (A2) "72B" then (A1) should be green color and (A2) should be yellow see below examble. If there is any formula please give me. Thanks in advance.
A B C
1 55j 24e 37D
2 72B 45r 64
3 58B 26 49D
4 48B 33 40D
5 38 37h 23
6 47B 34 42D


Thanks & regards
sanjay
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
in the first message you have written
qutoe
there is any capital text in worksheet that cell I wanted in lite green
unquote
inthe latest messge
quoe
(A2) should be yellow
unquote
A2 is having upper case(capital) at the end (72B)
I took latest expression is final

I also suppose you want to check each of the cells in the sheet and if the end letter in each cell is upper then yellow and if lower green and if there is no letter nothing to be done. On this basis try this macro (I do not think there is any formula in excel 2002 to conditionally color a cell )

Sub test()
Dim rng As Range, c As Range
Set rng = Range(Range("A1"), Range("A1").End(xlDown))
For Each c In ActiveSheet.UsedRange
c.Interior.ColorIndex = xlNone
If Not IsNumeric(Right(c, 1)) And LCase(Right(c, 1)) = Right(c, 1) Then c.Interior.ColorIndex = 4
If Not IsNumeric(Right(c, 1)) And UCase(Right(c, 1)) = Right(c, 1) Then c.Interior.ColorIndex = 6
Next c
End Sub

Posts
10
Registration date
Wednesday September 9, 2009
Status
Member
Last seen
September 8, 2010
1
Dear friend

Your gave that macro is working. Thanks. But that macro color all sheet I wanted only selected part. selected part will be anywhere not only (A1) ,(A2) . If I will select any part of data then macro should apply for selected part.
Thank you very much.
Posts
10
Registration date
Wednesday September 9, 2009
Status
Member
Last seen
September 8, 2010
1
And also one thing. If there is nothing anything in the cell like value and alphabets then it should be not color. It should be white like not color.

Thanks & regards
sanjay
Posts
10
Registration date
Wednesday September 9, 2009
Status
Member
Last seen
September 8, 2010
1
hi,
If there is in one cell Lower case and upper case both will come then then it should be green. For example this "29aeF" should be green. Beacuse there is upper case. And this "21bdf" should be yellow. Beacause there is no upper case alphabet.

Thanks & regards
sanjay
Posts
10
Registration date
Wednesday September 9, 2009
Status
Member
Last seen
September 8, 2010
1
Hi,

Sorry for some mistakes by me but this is my last statements and question to you.
We have lots of data. In the worksheet there are text comes with value. I want to change cell color base on there upper case and lower case. If there is any capital text in worksheet that cell I wanted in lite light green color. If there is any lower case text in worksheet that cell I wanted in lite light yellow color. selected part will be anywhere not only (A1) ,(A2) . If I will select any part of data then macro should apply for selected part. And also one thing. If there is nothing anything in the cell like value and alphabets then it should be not color. It should be white like not color. If there is in one cell Lower case and upper case both will come then then it should be light green. For example this "29aeF" should be light green. Beacuse there is upper case. And this "21bdf" should be light yellow. Beacause there is no upper case alphabet. Please give me any suggestion. Thanks in advance. for example.
1 55j 24e 24eb
2 72B 45r 64
3 58B 26 49D
4 48B 33 40D
5 38 37h 23
6 47B 34 42D