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

Conditional criteria with colored cells

fher9728

New Member
Hi community,

I've been doing some work in a table, and this table works a lot with dates, so the thing I wanted to do is that dates that are 15 days after todays date they'll be highlight in color yellow, I did that with no problems with conditional formatting, but the problem that I have is in the next step, I want that the cells that are highlight in yellow shows a Status that says "Vencida" and the cells that aren't highlight show status "Vigente", but I want to do this full with Vba, but without a vba function because I dont want to use formulas because the range of data is so extensive that the Workbook will be so big and I need to remain a fast and light workbook, I'll show the table:


1585148317119.png



The thing I want to do is like a conditional that if cells with range G are highlight with yellow color the status in range M shows me "Vencida" else show "Vigente",
I've been trying to do that but it only shows me Vigente, but the status doesn't change to Vencida, this is the code:

VBA Code:
Code:
Sub COUNT_HIGHLIGHTS()
'
' COUNT_HIGHLIGHTS
'


'Defining variables
Dim LastRow As Long, Count As String
Dim celdaOrigen As Range, rango As Range


'Getting a number value for the number of rows in the table (as this can vary on table size)
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "G").End(xlUp).Row


'For loop to loop through rows
For i = 2 To LastRow
'Count is the number of cells in the row which are highlighted
Count = "VIGENTE"
'For loop for cells within the row (My table always has 36 cells)
For J = 2 To LastRow
'If statement to check if cell is highlighted
If Cells(i, 7).Interior.Color = ActiveSheet.Range("U1").Interior.Color Then
'Add +1 to count ever time a highlighted cell is found
Count = "VENCIDA"
End If
Next J
'find cell at the end of the row and add the count
Cells(i, 13).Select
Selection.Value = Count
Next i
End Sub
SECOND POINT


1585148673415.png


In column H,J,K,L I have an vba code which if I put any text or value in Column B range it will throw me automatically todays date, like registration date in the table, the problem that I have is that I need to copy paste data from another table and if do that it doesnt shows me automatically the registration date, I want to copy and paste the data un column B and automatically shows me the date in column G, also I want to let me undo the action because when I modify something in this two columns it doesn't let me undo any action:
1585148939568.png


the vba code for this is :
VBA Code:


Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Range("B:B")) Is Nothing Then

Range("H" & Target.Row) = Date

Range("I" & Target.Row) = Format(Now, "hh:mm")

End If

If Not Application.Intersect(Target, Range("J:J")) Is Nothing Then

Range("K" & Target.Row) = Date + Target.Value

End If

If Not Application.Intersect(Target, Range("K:K")) Is Nothing Then

Range("L" & Target.Row) = Target.Value

End If
End Sub
Pleaaase I need help, thankyou

>> Please, use code - tags as written in Forum Rules <<
 
Last edited by a moderator:

Marc L

Excel Ninja
Hi,​
your first code has a lack of Logic and as it is written - loop - it could be slower than formulas in column​
so it's better - and easier to maintain for beginners - to use even under a VBA procedure a formula - without any loop -​
as a formula can be replaced by its result … (*)
For your second point, as soon as a VBA procedure is executed the Undo Excel feature is not available,​
Undo is only possible after a manual operation.​
So if you need to 'Undo' after a VBA procedure you must reload the workbook as it was before launching the procedure​
so you just must save the workbook before.​
For further help attach a workbook sample and for each point a complete technical explanation of the need​
with before and after contexts examples accordingly, in order there is nothing to guess for any helper …​
(*) Start with a formula in column and once it works, post it here then it will be easy to show you how to use it within a VBA procedure.
 

p45cal

Well-Known Member
For point 1:
Code:
Sub AddStatus()
Dim LastRow As Long, myColour, i As Long

LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "G").End(xlUp).Row
myColour = ActiveSheet.Range("U1").Interior.Color
For i = 2 To LastRow
  With Cells(i, "M")
    If Cells(i, 7).DisplayFormat.Interior.Color = myColour Then .Value = "VENCIDA" Else .Value = "VIGENTE"
  End With
Next i
End Sub
If this is too slow, come back and I'll speed it up, but you'll need to give me the conditional formatting rule. Best attach a workbook, not pictures.
 
Last edited:

p45cal

Well-Known Member
Grrrr.
You've cross posted without supplying links.
Wasted my time. You could've guessed, or at least checked the rules here too.
 

fher9728

New Member
For point 1:
Code:
Sub AddStatus()
Dim LastRow As Long, myColour, i As Long

LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "G").End(xlUp).Row
myColour = ActiveSheet.Range("U1").Interior.Color
For i = 2 To LastRow
  With Cells(i, "M")
    If Cells(i, 7).DisplayFormat.Interior.Color = myColour Then .Value = "VENCIDA" Else .Value = "VIGENTE"
  End With
Next i
End Sub
If this is too slow, come back and I'll speed it up, but you'll need to give me the conditional formatting rule. Best attach a workbook, not pictures.
It works fine, thankyou so much.
 

fher9728

New Member
Grrrr.
You've cross posted without supplying links.
Wasted my time. You could've guessed, or at least checked the rules here too.
Sorry I didn't knew about cross posts until yesterday, but you didn't waste your time I'm trying to analyze the different ways to structure a code for a case like this, thank you!
 
Top