Previous Topic Next Topic

Color Column Range Based On Content In Cell

Solved/Closed
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 - May 20, 2011 at 11:57 AM
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 - May 22, 2011 at 09:53 PM
Hello Sir,

I want to know how to color the column based on content in fixed cell like.

In Row No 6, If The Value Of Row 6 Is "Sun" Then Color That Column From 6 To 36.


I had tried this with conditional formatting but didn't find the solution.

=Indirect("C"&Row())=Sun this is working for row. but i want to know for column.

Thanks In Advance


Regards,

GameStartNow

4 replies

Answer 1 / 4
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
May 21, 2011 at 09:40 PM
for coloring whole window, I think you need a macro 

Sub test()
Dim cfind As Range
Set cfind = ActiveSheet.UsedRange.Cells.Find(what:="sun", lookat:=xlWhole)
If Not cfind Is Nothing Then
Range(Cells(6, cfind.Column), Cells(36, cfind.Column)).Interior.ColorIndex = 3
Else
MsgBox "that word is not available in the sheet"
End If

End Sub


color index 3 for red, 4 green.5 blue,6 yellow
0
Answer 2 / 4
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
May 22, 2011 at 12:03 PM
Nice Work Sir

I want this works on cell change like. Means for every time i have to run Macro.

I Want to run by their self when cell.value change.

Please change this formula like:

you have defined the range from 6 to 36.

i want you to define the row like on row # 6 for find that word "Sun" & then if found then color that column from 6 to 36. & if not found then the color would be white. In your given module after running it color the column but if i delete that sun from that row & then run the macro it shows the message but didn't clear that color.


hope you understand what i am trying to say.
0
Answer 3 / 4
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
May 22, 2011 at 08:43 PM
"you have defined the range from 6 to 36.

i want you to define the row like on row # 6 for find that word "Sun" & then if found then color that column from 6 to 36"

suppose "sun" is in F9. do you want F6 to F36 to be colored and you want all the rows 6 to 36 to be colored. it will be clumsy.

range frolm 6 to 36 means the intersection of row 6 and column F to the intersection of row 36 and column F

now right click the sheet tab of the data and click view code.
in the resulting window copy this EVENT CODE. the event will be fired only if you have "sun" in any cell. I HAVE ASSUMED THAT THIS STRING "SUN" WILL OCCUR ONLY IN ONE COLUMN AND NOT IN MANY COLUMNS.

FIRST TRY THIS IN AN EXPERIMENTAL FILE. DO SOME EXPERIMENTS. TYPE SUN ANYWHERE SEE WHAT HAPPENS. DELETE THAT ENTRY WHAT HAPPENS. AGAIN ENTER IN SOME OTHER COLUMN SUN AND SEE WHAT HAPPENS.

If you are satisfied then use the event code in your original file.

The event code is 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cfind As Range, add As String
ActiveSheet.Cells.Interior.ColorIndex = xlNone
Set cfind = ActiveSheet.UsedRange.Cells.Find(what:="sun", lookat:=xlWhole)
If Not cfind Is Nothing Then
add = cfind.Address
If Target.Column <> cfind.Column Then Exit Sub
Range(Cells(6, cfind.Column), Cells(36, cfind.Column)).Interior.ColorIndex = 3
Else
MsgBox "that word is not available in the sheet"
End If


End Sub
0
Answer 4 / 4
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
May 22, 2011 at 09:53 PM
Nice Work Sir.

Thanks Alot :)


Regards,

GameStartNow
0