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

color cells depend on condition

S

sparcle

New Member
Hi all,


i have 2 cols in a work sheet.one contain sl no & other time values like 17-May-12.

I want a macro, when i select a cell & run it it will color those slno cells which has less time value than selected row.
 
Off the top

[pre]
Code: 
With Activesheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow

If .Cells(i, "B").Value < Now() Then

.Cells(i, "A").Interior.Colorindex = 3
End If
Next i
End With
[/pre]
 
Hi ,


Try this :

[pre]
Code: 
Public Sub color_cells()
Dim data_range As Range
Set data_range = Range("F2:G21")      ' Change as required
data_range.Interior.ColorIndex = 0
selected_date = ActiveCell.Offset(0, 1).Value
ActiveCell.Interior.ColorIndex = 8
ActiveCell.Offset(0, 1).Interior.ColorIndex = 8
data_range.Select
number_of_rows = Selection.Rows.Count
For i = 1 To number_of_rows
If ActiveCell.Offset(i - 1, 1).Value < selected_date Then
ActiveCell.Offset(i - 1, 1).Interior.ColorIndex = 6
End If
Next
End Sub
[/pre]
I have assumed that your two columns are adjacent columns ; in the code they are F and G.


Before running this code , change the range address F2:G21 to whatever you want ; place your cursor on the desired cell ( not the cell which contains the date value , but the other cell ) , and then run the macro.


When you run the macro a second time , the cells which were already colored will lose their color formatting , and depending on the newly selected date will be reformatted.


Narayan
 
@xld,


thank you for your answer but it color all the cells.


@NARAYANK991


thank you sir.

It works fine but sir in my real world scenario date value in F col & want to color A col.
 
Hi Sparkle,


You can do the same with Conditional Formatting also.


Just write the below block in VBA > Worksheet > Selection Change

[pre]
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 2 Then Calculate
End Sub[/pre]

then select the column with Date value and use the below condition formatting..


=$B2>OFFSET($B$1,CELL("row"),0)


assuming your date is in 'B' Column..

If you want to face the real world scenario, then manage rule and extend the 'apply the same rule' i.e from =$B$2:$B$410 change it to =$A$2:$f$410


Now please click any date value in 'column B', and see desired result.. :)


please see the attached..

https://www.dropbox.com/s/vxw337pk71jx7ix/Conditional%20Formatting%20for%20sparcle.xlsm
 
Hi ,


If your range is such that the dates are in column A , and you will select any cell in column F , then use the following revised code :

[pre]
Code: 
Public Sub color_cells()
Dim data_range As Range
Set data_range = Range("A2:F21")      ' Change as required
data_range.Interior.ColorIndex = 0
selected_date = ActiveCell.Offset(0, -5).Value
ActiveCell.Interior.ColorIndex = 8
ActiveCell.Offset(0, -5).Interior.ColorIndex = 8
data_range.Select
number_of_rows = Selection.Rows.Count
For i = 1 To number_of_rows
If ActiveCell.Offset(i - 1, 0).Value < selected_date Then
ActiveCell.Offset(i - 1, 0).Interior.ColorIndex = 6
End If
Next
End Sub
[/pre]
Regarding your second post , about column K values not equal to y , can you say what this y is ? Will it be a fixed cell value ?


Essentially what you are saying is that instead of one condition involving the dates within a column , there will now be two conditions , one involving the dates , and the other involving cells in column K being less than a specified value ; is this correct ?


Narayan
 
Hi Sparkle,


regarding your second condition, I would like to say, you can still do the same via conditional formatting..


you just need to update the conditional formatting as

Code: 
=AND($B2>OFFSET($B$1,CELL("row")-1,0),NOT($K2=$Y2))


Please download the updated sheet again..(from the same link)


I would like to support NARANK991's coding, as you are frequently changing the requirement..

and VBA can easily adapt.. ISDATE(), ISHOLIDAY(), ISWEEKEND() like UDF..

:)
 
@NARAYANK991,


y is a fixed character value.

i mean if K col value is not equal to y then color those cells which has less value than selected row date value.
 
@Debraj Roy,

thank you for your valuable answer.

it works but it color those cells which has greater values than selected cell.
 
OOPS.. my Mistake..


Code: 
=AND($B2<OFFSET($B$1,CELL("row")-1,0),NOT($K2=$Y2))


use the above code in Conditional Formatting..
 
Hi ,


Try the following code :

[pre]
Code: 
Public Sub color_cells()
Dim data_range As Range
Set data_range = Range("A2:F21")      ' Change as required
data_range.Interior.ColorIndex = 0
selected_date = ActiveCell.Offset(0, -5).Value ' -5 is because column A is 5 columns to the left of column F
ActiveCell.Interior.ColorIndex = 8
ActiveCell.Offset(0, -5).Interior.ColorIndex = 8  ' -5 is because column A is 5 columns to the left of column F
data_range.Select
number_of_rows = Selection.Rows.Count
For i = 1 To number_of_rows
' 10 is because column K is 10 columns to the right of column A
' If column K cells will need to be checked for any character other than Y
' change the next statement to reflect this.
If ((ActiveCell.Offset(i - 1, 0).Value < selected_date) And (UCase(ActiveCell.Offset(i - 1, 10).Value) <> "Y")) Then
ActiveCell.Offset(i - 1, 0).Interior.ColorIndex = 6
End If
Next
End Sub
[/pre]
This will color those cells in column A , where the corresponding cells in column K do not have the character 'y' ( or 'Y' ) in them , and where the corresponding dates in column A are less than the date corresponding to the active cell in column F ! I hope this is clear.


Narayan
 
@Debraj Roy

thank you for your help.But i don't find where to put =AND($B2<OFFSET($B$1,CELL("row")-1,0),NOT($K2=$Y2)) this conditional formula.
 
@NARAYANK991,

thank you sir for your kindly help.It works fine.

is there any possible that color cells will below the selected cell when run another macro.
 
Hi ,


I am not sure about your requirement ; can you please clarify ?


Whenever you select a cell , what cells should be colored ? Cells below the selected cell can mean physically below that cell , or date-wise below ( or less than ) the date in the row where the selected cell is.


Narayan
 
Hi NARAYANK991,


in this topic you sir already solve the problem & color cells according condition.


i want those color cells will move below the selected cell i.e. date-wise below ( or less than ) the date in the row where the selected cell is when run another macro.
 
Hi ,


OK. As I understand it , you want another macro , which when it is run , will shift all the cells that are colored to the rows below the active cell.


Let me work on it , and I'll get back to you.


Narayan
 
Hi NARAYANK991,

Can i get some help from you.


If i write a formula on one cell of a column then how it will reflect on all cells of that column.
 
Hi,


i want color cells will move below the selected cell i.e. date-wise below ( or less than ) the date in the row where the selected cell is when run another macro.


Any suggestion for this problem.
 
You must log in or register to reply here.
Back
Top