Change color base on upper case and lower casClosed

Question

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

venkat1926 · Answer

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

sanjayjagtap · Answer

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.

venkat1926 · Answer

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

venkat1926 · Answer

did you try the macro? what probllem you had.

venkat1926 · Answer

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

sanjayjagtap · Answer

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

Change color base on upper case and lower cas

6 responses

Similar discussions

Subscribe To Our Newsletter!