• 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.

Help on VBA scripting

R

Raja Kamarthi

New Member
Hello Friends,

I have a sheet which has conditional format applied to a set of cells. I applying a VBA script (pls refer below) in this sheet to hightlight the "ACTIVE CELL" but when I am doing so, the current existing conditional format applied for other selected cells gets deleted. Is there a way to fix this?


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Cells.FormatConditions.Delete

With Target

.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"

With .FormatConditions(1)

With .Borders(xlTop)

.LineStyle = xlContinuous

.Weight = xlThin

.ColorIndex = 15

End With

With .Borders(xlBottom)

.LineStyle = xlContinuous

.Weight = xlThin

.ColorIndex = 15

End With

End With

.FormatConditions(1).Interior.ColorIndex = 20

End With


End Sub


Thanks

Raja Kamarthi
 
Its been quite a while now looking out for someone to resposnd to my query.

Friends, please take out sometime and help me out as this is on high priority.

I just need a VBA script which highlights the active cell and at the same time doesnt delete the active conditional formatts in the sheet.


Thanks in advance

Raja
 
Hi !   LoL ‼   ->   Just remove the second line ‼   (
Code: 
Cells.FormatConditions.Delete
)


This line remove the format conditions for all the sheet's cells ‼
 
@Raja


Hi


i can't get the point from you actually what do you want


as per my understand try to replace the code with


cells.FormatConditions.Add


Other wise please upload a file


Thanks


SP
 
Hi Raja,


I have not tested this extensively but see if this works for you. You can add formatting conditions of your liking.


It does not use conditional formatting.

[pre]
Code: 
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static oRange As Range

If oRange Is Nothing Then

Set oRange = Target

With Target.Interior
.ColorIndex = 15
End With

Else

With oRange.Interior
.ColorIndex = xlColorIndexNone
End With

Set oRange = Target

With Target.Interior
.ColorIndex = 15
End With

End If

End Sub
[/pre]
 
@ Marc L: Thanks for your comment but unfortunately it was of not much help


@ sgmpatnaik & shrivallabha: Manys Thanks for your inputs. May be I should I have put my query in a precise way for your better understanding. I tried hard to upload the file but all my efforts have gone in vain.


I have 2 VBA codes which I want to run parallely. Below are the codes:

Code 1:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Range("C13").Value = activecell.Value

End Sub

Code 2:

Code shared by shrivallabha (above)


Can we link both the codes and have them run simultaneously?


Thanks,

Raja
 
@Raja


Hi


Please add the code before the Vallabha Code


Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Range("C13").value = activecell.Value

Static oRange As Range


If oRange Is Nothing Then


Set oRange = Target


With Target.Interior

.ColorIndex = 15

End With


Else


With oRange.Interior

.ColorIndex = xlColorIndexNone

End With


Set oRange = Target


With Target.Interior

.ColorIndex = 15

End With


End If


End Sub


Thanks


SP
 
@ sgmpatnaik: Much Thanks bro but when I tried running the script shared by you, I find only a part of code being applied i e "Range("C13").value = activecell.Value" is working absolutely fine but the active cell is not getting highlighted/formatted.


Please look into this


Thanks,

Raja
 
@Raja


Hi


You mean you want to color the Cell C 13 also including the Selected Cell


is this correct


Thanks


SP
 
@sgmpatnaik: I'm not sure if I got the right way of uploading a file, please click on the link to access the file:


https://www.dropbox.com/s/p105tbpzwiwpsjl/Sample%20Sheet.xlsm?m


Do let me know if you are unsuccessful in doing so.


Thanks,

Raja
 
@sgmpatnaik: In the spread sheet, when I click on the employee name, the same data gets reflected in cell C13 for eg if I click on cell E11 (Syed Nazim Quadri), this name gets reflected in cell C13 as well. Now I want the cell E11 to get highlighted with some conditional formatting.


Thanks,

Raja
 
Hi Raja..


Nice work..


*> Please select C7 to H11.

*> Apply Conditional Format as:

Format Only cell that Contain >

Cell Value >

Equal to >

=$C$13


Set Format and Apply..

Regards,

Deb
 
@Debraj Roy: Many many Thanks for your input. I spent a good amount of time looking out for a solution and now finally your resolution has brought a much needed relief to me.


A big thanks to all the excel experts who have been helping me out unconditionally.


Regards,

Raja
 
@Raja


Hi


sorry for my late replay yesterday my net was disconnected suddenly


Please check this file


https://dl.dropbox.com/u/75654703/Sample%20Sheet.xlsm


Have a nice day


Thanks


SP
 
You must log in or register to reply here.
Back
Top