Previous Topic Next Topic

Color Column Range Based On Content In Cell

Solved/Closed
Report
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
 -
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
 -
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
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 803
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
Answer 2 / 4
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
 7
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.
Answer 3 / 4
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 803
"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
Answer 4 / 4
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
 7
Nice Work Sir.

Thanks Alot :)


Regards,

GameStartNow
Ask a question