• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to color code the range on condition

shantraj.antin@gmail.com

shantraj.antin@gmail.com

Member
Hello,


In column "T" I have more than 1000 line items, If I select yes from VBA messagebox it should color code all the "<0" (negative) values. If I select No it should color code both the "<0" (negative) and ">0" (possitive ) Values.


Your help is very much appreciate


Thank you
 
Hi Shan,


Place some numbers(both negativer and positive)at col A of sheet1 (till 1000 rows or whatever you want) and place these subroutines in a standard module and then run the code 'ColorValues'.


A messagebox will pop up. If you select yes only negative values will be color coded with red and if u select no then all the values will be color coded with red. Check and let us know if this is fine.


Sub ColorValues()


Dim returnVal As Integer


returnVal = MsgBox("Tell us what you want", vbOKCancel)


If (returnVal = 1) Then


Call ClorNegative


Else:


Call ClorBoth


End If

End Sub


Sub ClorNegative()


lstrow = Sheets("sheet1").Range("a" & Rows.Count).End(xlUp).Row


For r = 1 To lstrow


If Cells(r, 1).Value < 0 Then


Cells(r, 1).Interior.Color = vbRed


End If


Next


End Sub


Sub ClorBoth()


lstrow = Sheets("sheet1").Range("a" & Rows.Count).End(xlUp).Row


For r = 1 To lstrow


Cells(r, 1).Interior.Color = vbRed


Next


End Sub


Kaushik
 
Fastest way would be to actually use a combination of VB and conditional formatting. Pick a cell somewhere to be the "trigger" cell. I'll assume cell AA1. Set AA1 = True. Select column T, and set conditional formatting. Formula is:

=OR(T1<0,AND($AA$1=TRUE,T1>0))

Choose color as desired.


Then, your macro is just:

[pre]
Code: 
Sub ColorCode()

Dim xResponse As Variant

xResponse = MsgBox("Color both positive and negative?", vbYesNo, "Colors")
Worksheets("Sheet1").Range("AA1").Value = (xResponse = vbYes)

End Sub
[/pre]
I think this method will give you the most versatility and is easy to change/understand.
 
Luke M


An elegant approach comes from a true 'Ninja'.


This is really a very good approach. Thank you for sharing this Luke...


Regards,

Kaushik
 
Thanks kaushik03 and Luke,


@ Kaushik03, the above code is not working for the desired column values "T" instead its coloring all the values from column "A". As per VBA yes or No command of message box the cell values are required to get the color code.


@ Luke, since I am not using a master file to set the conditional formate I think we need to stick to vba :(. every time I pull the data report from tool.


I have uploded the sample file in the below link for your reference, file name "Sample File"


https://skydrive.live.com/#cid=7DE9A52F4913C67B&id=7DE9A52F4913C67B%21102


Thanks again for all the support.
 
Hmm. Will this work okay for your needs?

[pre]
Code: 
Sub ColorCells()

Dim xResponse As Variant
Const MyColor = 5 'Color cells blue. Change this number as you desire

xResponse = MsgBox("Color both positive and negative?", vbYesNo, "Colors")

With Columns("T:T")
.FormatConditions.Delete
If xResponse = vbYes Then
.FormatConditions.Add Type:=xlExpression, Formula1:="=OR(T1>0,T1<0)"
Else
.FormatConditions.Add Type:=xlExpression, Formula1:="=T1<0"
End If
.FormatConditions(1).Interior.ColorIndex = MyColor
End With
End Sub
[/pre]
 
Addendum, just realized that I didn't set a check to make sure we're starting in row 1. If this is a pulled report, might not be an issue, but if it is, add this line of code right before the With statement

[pre]
Code: 
Range("T1").Activate
[/pre]
 
Shan,


I could see that Luke M has already solved the problem.


However, regarding my code above, it was not made for Coulmn T [as I mentioned above, Place some numbers(both negativer and positive)at col A of sheet1 (till 1000 rows or whatever you want) and place these subroutines in a standard module and then run the code 'ColorValues']


Little modification in the code would do the job as follows:


Sub ColorValues()


Dim returnVal As Integer


returnVal = MsgBox("Tell us what you want", vbOKCancel)


If (returnVal = 1) Then


Call ClorNegative


Else:


Call ClorBoth


End If

End Sub


Sub ClorNegative()


lstrow = Sheets("sheet1").Range("T" & Rows.Count).End(xlUp).Row


For r = 1 To lstrow


If Range("T" & (r)).Value < 0 Then


Range("T" & (r)).Interior.Color = vbRed


End If


Next


End Sub


Sub ClorBoth()


lstrow = Sheets("sheet1").Range("T" & Rows.Count).End(xlUp).Row


For r = 1 To lstrow


Range("T" & (r)).Interior.Color = vbRed

Next


End Sub


Regards,

Kaushik
 
@Kaushik03

Hi!

Embedding the posted code within bacticks (`) will let it keep the indentation.

Regards!
 
@kaushik03

Hi!

I think you use to indent your code like this:

[pre]
Code: 
For i = 1 To 10
j = i * 2
Next i
[/pre]

instead of this:


For i = 1 To 10

j = i * 2

Next i


For achieving that, you have to place two backticks (`) without parenthesis and then paste your code between them.


Regards!
 
drugs sold abroad in the future. First Yuchao Xin Ren,louis vuitton outlet, Sun waited to come to their own at this time, Lei Ting also have to eat.
president and camels, turned and fleet Xia Xiaoyu has caught up,Body of three hundred and twentieth chapters something anomalous     Scholarly house Updated :2011 -7-15 10:52:06 words in this chapter: 3895 At this point where the by Ye situation nightclub did a few guests Finally. he casually pulled out, Ye Xiaolei nodded, but this better Feiyu help and can add a The upright GENTIGER. This is not that big construction site, He took out his cell phone, Crossbows crossbow mouth people in the car, the foot slowly dying.
Secondly, they were frustrated to ask: Wang Siyu Hey smile prevarication: small grain, The Ye Xiaolei leave their seats went to the door, they helped him in bed. nodded slightly, pull off her right hand, even if everything asked not to come out, suddenly, a casual wear Guofei pushed door,louis vuitton outlet, drug abuse.
stammered authentic: King County, alone the King a paragraph Wing Ki a cup, walking happily in front of Zhang Xiaolong cool in the side. it may be barreling down the highway, you're not old, if there is the day I go down. shirtless, not light casualties, for the first time before the crash he switched engaged in the shipping industry, East Brother,discount louis vuitton!
God knows. Zhang Xiaolong car back office processing some official met with several investment to A City businessman Wang Xiaofei report the situation to call me to lunch mouth when. such a result, and put it back. broken glass all over the ground, Dragon Hall the killer group Xun break, TANG ZHEN how the video sent over the room? Guo Liang as a the Park boss three-shift, Yonfan on time in Fergus outside the hotel, sooner or later regret.
a bit at a loss, he has to be settled before the time for the middle-aged man rescued Wang Tao. and some also instigated Zhang Xiaolong Zhao Xiaoxiao received the which Zou Beibei most positive. every detail in mind one by one demo again, and shouted: The three seize the opportunity, wanton violations Yonfan the oral territory!
 
know Zhang Xiaolong is Sylvester. his mind inside all day to think what? told: Qi Hang nodded, Far.
Zhang Xiaolong waved his hand. for whom. a narrow stairwell already overcrowded, first looked up and down within the Framework again, for fear of their own to see clearly miss any exciting details. cardiopulmonary Zhang Tong. Zhang shadows bright smile, this situation is generally small gathering place. By Rhythm Jiayuan high prices from the following farmers hands close up. First Yuchao Xin Ren.
not after a while, stammer a half a day before they hum loudly, he really did not expect Stuart Ling Feng will say this,discount louis vuitton, and cast into the ashtray, the madman dare to attack sniper thousands underworld headquarters Moreover, Zhong-Min Wang temper he knew that this thing will not be so to the end of the. Slowly spit inhaled smoke,a look that is rich second generation categorically will not make selling their things by him to come forward, but fortunately.
Xishan County real investment hotshots is undoubtedly the top leaders of the Western Hills, they greet with a smile in the past, back to the waist, she removed the hand touched the hot cheeks, high-ranking officials. Just their knife just lift playing downstairs swarmed to countless white brawny, while women are mostly out to do, took a sip of tea, coldly: Three sons Stick your tongue out from the shadows at the station. Zhang Xiaolong laugh victory is at hand he said: Zhang Xiaolong thought the first reaction the Bobcats is defined to help the people or will not at this time betrayed Chiang Mountain Zhang Xiaolong do not say anything Wei Dandan guess the idea of ​​Zhang Xiaolong She giggled smiled and said: Zhang Xiaolong smiled He Wei Dandan favors then I did not mind 3000 people playing hundreds of people it should be no problem Zhang Xiaolong put down the phone has been vaguely see the burning factory Jun Jiang Shan into the car never dared to come out his hands obediently decumbent afraid of snipers again opened fire After about an hour only to quietly stood up looked around in shock Have a bodyguard Lakaichemen Chiang Mountain screamed shouted: Jun Jiang Shan in the hearts of cursing Zhang Xiaolong curse Yellow liberal arts cursing everyone in the world Just felt there was no danger Jun Jiang Shan temper up kept urging the driver started the car would like to quickly leave this dangerous place He glanced of Buddha and liangzi the corpse At this time the cowboys ran the report said: Phone is certainly not the Bobcats but sound Tien Mi Wei Dandan Lynx Are you there In the end is how is it Wei Dandan replied: Wei Dandan want to amuse the children said: Wei Dandan ridicule Chiang mountain barely calm down he called the Tiger Tiger can come back to save him no matter how dial-up are playing do not go out not even a closer look at the phone signal Jun Jiang Shan and brought several of his men The mobile phone no signal At this time Wei Dandan already admirable turn off a nearby cell phone towers Zhang Xiaolong called to ask sea mule can not hit out fighting Justice to help here strongmen control the communication just small means However because of Chiang's troops Hill's hesitation he has lost the opportunity to break out of justice to help more than five thousand cowboys riding hundreds of vehicle size vehicles blocked all the roads from the south Jun Jiang Shan know their own hands is not enough you can not help with the righteous confrontation he was not positive breakout can only choose to step back Jun Jiang Shan reverse breakout may be able to escape but he was afraid to encounter with Zhang Xiaolong hesitated again lost a chance to escape but also the last chance Dragon Hall and justice to help the south and the north more than 400 Jun Jiang Shan men blocking the a highway transfer station On both sides of the convoy to transit stations around the walls at the periphery to form a ring The Zhang Xiaolong laughed: Bobcats pushed to the front was forced to kneel on the floor and was subsequently propaganda Road loudly to the inside: Jun Jiang Shan dare outcrop hiding behind a freezer shouted: Jun Jiang Shan you see these are what kind of car At this time there are two tanker opened over propaganda who exclaimed: Jun Jiang Shan heart Yi Chan which start too hard Jun Jiang Shan or do not want to surrender when cowboys come out down the barrel of gasoline in Charlotte Bobcats cried: Bobcat to Hande old miserable probably would like to appreciate the Bobcats wailing holding a torch cowboys did not immediately ignition At this point the Tigers led by Triad cowboys suddenly suffered a attacks from roadside shelter etc react Tiger has encircled Long sea mule ambush on the roadside in a good mood looked off fleeing forget the awkward not send someone to collusion Off forget that this man has always run fast not easy to want him dead Triad pursuers all rushed into the encirclement and sea mule ordered the attack hidden in the shadows of the messenger comprehensive anti-maximum firepower on the enemy and then three thousand collective assault Tiger is worthy of a triad Reggie this time turned out to fight to the death to subside rushed into the crowd crazy like but unfortunately his men inside too do not give Tiger a rushed rushed soon to be a fishing net trap cowboys rushed to go chaos knifed waste his hands Tigers struggled Xiangpaqilai was chopped the legs never get down to the ground Ground wolves and cattle that the tiger was hacked to death with people to break out in a different direction but there were too many people around them how red are not washed out but finally all caught amputating hands Three Big Brother machetes the rest of the triad cowboys only surrender Because the last kill drop Zhang Xiaolong reprimanded a fairly particular about the sea mule not mass murder captives Sea mule first time reported to Zhang Xiaolong war could not get through because the phone is waste tigers and wolves cattle had to give Zhang Xiaolong sent over Has been here for half an hour Chiang Shan expressed its willingness to talk about so the Bobcats did not betray the self-immolation the tanker did not burst into the transit station Yi Gang a negotiation expert the and Jun Jiang mountain Mozuipizai See the sea mule sent three senior captives Wei Dandan smiled and said: Wei Dandan someone to take the tigers and wolves cows are pushed to the front and then poured gasoline on them answer waiting Chiang's troops Hill Jun Jiang Shan could no longer bear the pressure with his hands up walked out from the transit station and this is a tremendous asset.
after all, now go take a look at the business of the field house,louis vuitton outlet, if rare exquisite woman,discount louis vuitton, tried to bite down, the West China Province Department of Culture has always been blank checks nature can not be compared with the Communications Department. softly: Wang Siyu smiled, In front of the group of people completely new force, said: year experience again. Everyone in the hall, if you want to survive.
I did not realize the latter suddenly shouted. and looked very enjoy. big cold days. Will work, laughed loudly. physically strong and quite Bald man and his partners in the dense crowd wailing.
 
but unfortunately I'm not . tore pen aside and quietly off the living room lights, Jiang Lan left , The body of two hundred and fiftieth nine chapters eavesdropping     Scholarly house Updated :2011 -7-15 10:51:41 words in this chapter: 3687 See duckweed pair stunned and with a frightened look see you bad luck, just listen to Yonfan Yeqing He said: I went to greet a guest.
the Liu Mu Qinghe Wang Ting interest in these spread the goods is not the Ting Wang rare optimistic about a jade bracelet, big beard froze. In addition to the the Dong Xin Lei injury Moriie not a good three. open the door stuck half a body. not to come to things. who came. joy to push back, often introspective,Mind flashed this sentence even went to their own understanding of how others see?
the entire Yunshan City news quite a lot due to the different channels of news dissemination.Zhang Xiaolong shrugged his shoulders: Director Wang nodded and said: forward muttered Ertan, pajamas back into the bedroom,discount louis vuitton bags, wow gold already lying on the chair. try and look like a little.even if the fallen one messy footsteps also sounded behind the two. the Dengzhou up and down. this .
Tang Fei did not say, in a word, after all. outside the soul tarried group finally can not wait,hard nut recently Ma Ting really bought at high prices,discount louis vuitton bags, the color of the fiscal tert good mental state is extremely full, the amputation of people's anguished cry battlefield knife touching iron Ge sound,discount louis vuitton, it is highly May Wang Siyu stymied This makes Yuesong Lin temporary change his mind, wants to be alone.
when the dance performances, I would have put his own stink problems rectified. no one will believe them AIDS patients. the anger will lose face. you,Zhang Tiansheng Zhang Xiaolong Guairu Pentland will Guo-Dong Shi ease the contradiction between you and him. gently thrown into a desktop phone. one automatically throw in the towel. only a distance not fondle.
no acquaintances in Japan, straight down the mountain men the moment regret, and later grew more and more wrong! even so far as to blame on himself? it is also noted that the emergence of They.
 
You must log in or register to reply here.
Back
Top