Color Column Range Based On Content In Cell Solved/Closed

Question

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

venkat1926 · Answer

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

Game Start Now · Answer

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.

venkat1926 · Answer

"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

Game Start Now · Answer

Nice Work Sir.

Thanks Alot :)


Regards,

GameStartNow

Color Column Range Based On Content In Cell

4 responses

Similar discussions

Subscribe To Our Newsletter!