Give me any macro or formula for color cells

Closed
sanjayjagtap Posts 10 Registration date Wednesday September 9, 2009 Status Member Last seen September 8, 2010 - Sep 17, 2009 at 11:04 PM
 sanjay - Sep 28, 2009 at 10:37 PM
Hello,

I 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

Thanks & regards

2 replies

Let's say your data is in column A:

1 55j 24e 24eb
2 72B 45r 64
3 58B 26 49D
4 48B 33 40D
5 38 37h 23
6 47B 34 42D

Then you put in column B a 1 for lowercase and a 2 for uppercase. Column B looks then like this:

1
2
2
2
1
2

Now select cell A1 then select from the top menus the option conditional formating.
Select Formula is, in the next field you put =B1=1.
Add another condition, select Formula is, in the next field you put =B1=2.
Close the conditional formating menu by clicking OK.
Now drag the cell down. All your data is gone now, but don't worry.
Look for the square with additional options and select the 3rd option from the top (something like "carry through format only").

Depending on how much data you have this could take a lot of time.

You could also use the "Find and replace" option (Ctrl+H).
Click on the additional options, this lets choose format as well.
Search for the letter A with no format and replace it with letter A with a different background color.
Then choose replace all.
Repeat this for every letter of the alphabet.

Hoping one of these solutions will benefit you,
Kind regards,
Trowa
1
Dear Trowa,

Thanks for the answer me.

I satisfied with your answer.

But I have a very big data I was looking for any macro vba formula or any other excel formula. Beacause It can save my time.

Thank you again
0
Trowa > Sanjayjagtap
Sep 25, 2009 at 07:49 AM
I understand Sanjayjagtap, unfortunately my VBA knowledge is too poor to write a code for you.

If you have still some time to wait you could post a new thread asking explicitly for a VB code, since there are some great code writers checking out this forum.

Kind regards,
Trowa
0
sanjay > Trowa
Sep 28, 2009 at 10:37 PM
Thanks Trowa.
0
The easiest way I can think of is to create an extra column, which you can hide if you don't want to see it.
In this column you put a "1" for cell containing upper cases and a "2" for lower cases.
Then use conditional formatting to change the color of the cell based on the 1 or 2 put in the column mentioned above.

Best regards,
Trowa
0
Hi

Actually I am using MS Excel 2007. Thanks for you gave me answer. But I am still confuse please give me step by step information.

Thanks & regards
sanjay
0