fill color and border in dynamic range

[tikistat]

HI all,

I need your help

In column A (Program), I need to fill color (white, background) the entire row where ever I encounter a word "submit" untill last blank row i.e. dynamic range.


After that I have to make entire border from cell(2,2) to last row and column in the dynamic range. (entire border will be from kk in name variable to last m/f in gender)


program name age gender


EP1 kk 23 m

EP2 KK 24 F

EP submit 2 . m/f

RP1 MM 93 m

RP2 NO 74 F

RP submit 2 2 m/f


N.B. EP total. RP total ... etc are single word separated with one space and present in first column(plz dont consider total in name variable)

 

[Faseeh]

Hi tikistat,


This can be done by placing formulas in Conditional Formatting Toolbar. See whether this file fulfills your requirement or not.


Here is the file:

http://www.2shared.com/file/t2xVNLJz/Copy_of_Formatting.html


Regards,

FASEEH

 

[ashokbioinfo]

Dear FASEEH


Whether we can write macro to do that.

 

[Faseeh]

Hi ashokbioinfo,


Sorry, I am not a programmer actually, so i can't. Why you need a macro because it is working without macro using conditional formatting...

 

[dan_l]

It is probably a conditional formatting thing.


You can try something like this though:

[pre]

Sub highlightrows()
Dim testcell As Range

For Each testcell In Range("B:B")
If testcell.Value = "submit" Then
Range(Cells(testcell.Row, 1), Cells(testcell.Row, 5)).Interior.Color = RGB(0, 0, 0)
End If

Next testcell

End Sub

[/pre]