Solved: Converting the VB code to VBA.

[ucijoinhitro]

Hi, I am very new to VBA. I need a help on converting the VB code to VBA code. In vb, I am filtering a datagrid view to highlight the cells
if the cells contain the time greater than 3:30 and less than 20:00.
I have attached a excel file for which the code should reflect. And also I have pasted the code used in vb to highlight the cells in datagridview.
Please remember the code below works for any number of records in the datagrid view.E.g. Today there might be 10 rows and 10 columns in a datagrid while tomorrow there might be 12 rows or 12 columns in datagrid - To keep it simple it should work for any number of rows and columns rather than being fixed .
Please help me in providing a new VBA code or converting the below vb code to vba.




Dim colCount as Integer = DGV.Columns.Count
If colCount > 1 Then
Dim startDate As Date = Date.ToDay.Add(New TimeSpan(3, 30, 0))
Dim endDate As Date = Date.ToDay.Add(New TimeSpan(20, 0, 0))
Dim cellValue As String, cellDate As Date
For i As Integer = 1 To colCount - 1
For Each row As DataGridViewRow In DGV.Rows
cellValue = row.Cells(i).Value.ToString
If Date.TryParse(cellValue, cellDate) Then
If cellDate > startDate AndAlso cellDate < endDate Then
row.Cells(i).Style.BackColor = Color.LightYellow
End If
End If
Next
Next
End If

[Kenneth Hobs]

Welcome to the forum!

What is the point in doing that if you don't have the DataGridView control? Did you want to do it with a worksheet instead?

I have worked with some spreadsheet controls in a userform if that is what you mean. Those OCX controls can be flaky and will not work at all with 64bit systems.

[ucijoinhitro]

Thank you for addressing my concern. I export my datagridview from vb.net to excel file - here's where the problem comes when I export to a excel sheet all the datas are copied but the highlighted colour in datagridview is not being highlighted in the exported excel file. to succeed this I wanna a run a macro which will highlight the cells in the excel file depending on the data which was exported from the vb.

Yes I need a vba/macro in a excel file.



Welcome to the forum!

What is the point in doing that if you don't have the DataGridView control? Did you want to do it with a worksheet instead?

I have worked with some spreadsheet controls in a userform if that is what you mean. Those OCX controls can be flaky and will not work at all with 64bit systems.

[Kenneth Hobs]

I thought as much. Sure, that is easily done.

In a Module:
Option Explicit

Sub NetToVBA()
Dim colCount As Integer
Dim startDate As Date, endDate As Date, d As Date
Dim i As Integer, row As Range, c As Range

Const cLightYellow = &H99FFFF '10092543

colCount = ActiveSheet.UsedRange.Columns.Count
If colCount <= 1 Then Exit Sub

startDate = TimeValue("03:00:00")
endDate = TimeValue("20:00:00")

Sheet1.UsedRange.Interior.Color = xlNone
'Exit Sub

For i = 1 To colCount - 1
For Each row In ActiveSheet.UsedRange.Rows
d = 0
Set c = row.Cells(i)
Debug.Print c.Address, IsDate(c)
If IsNumeric(c) Then d = c.Value
If d > startDate And d < endDate Then c.Interior.Color = cLightYellow
Next row
Next i
End Sub

FYI for future posts:
Even though that is not VBA code, you could paste code between VBA code tags and it would color code it for the forum for the commands that are in VBA. Code code tags would be better than Quote code tags.

[ucijoinhitro]

Wow..Excellent...you made just like it. The code works excellent. I appreciate. I thank you for ever. I rate this high useful post.

One more help..Is it also possible to create a new column which should contain the value of highlighted cells.Example lets say -- if the first row contains 6 highlighted cells then the new column value for the first row should contain the value 6. Is it possible using VBA. And again this should work for n rows and n columns.
Please your inputs are greatly appreciated.

[quote=Kenneth Hobs]I thought as much. Sure, that is easily done.

[Kenneth Hobs]

You can do it like this or use the function as a UDF after NetToVBA.

When testing, clear the blank columns and rows that aren't really blank and then click save and then run DoTwoSubs.

Sub DoTwoSubs()
NetToVBA
CountColorsInRow
End Sub

Sub NetToVBA()
Dim colCount As Integer
Dim startDate As Date, endDate As Date, d As Date
Dim i As Integer, row As Range, c As Range

Const cLightYellow = &H99FFFF '10092543

colCount = ActiveSheet.UsedRange.Columns.Count
If colCount <= 1 Then Exit Sub

startDate = TimeValue("03:00:00")
endDate = TimeValue("20:00:00")

Sheet1.UsedRange.Interior.color = xlNone
'Exit Sub

For i = 1 To colCount - 1
For Each row In ActiveSheet.UsedRange.Rows
d = 0
Set c = row.Cells(i)
If IsNumeric(c) Then d = c.Value
If d > startDate And d < endDate Then c.Interior.color = cLightYellow
Next row
Next i
End Sub

'Put interior color count for each row into column at end of each usedrange.
Sub CountColorsInRow()
Dim row As Range
Const cLightYellow = &H99FFFF '10092543
For Each row In ActiveSheet.UsedRange.Rows
If row.row <> 1 Then
row.Cells(, row.Columns.Count + 1).Value = RangeInteriorColorCount(row, cLightYellow)
End If
Next row
End Sub

' =RangeInteriorColorCount(B5:I5, 10092543 )
Function RangeInteriorColorCount(cRange As Range, iColor As Long) As Long
Dim c As Range
Dim theSum As Long
Application.Volatile
theSum = 0
For Each c In cRange.Cells
If c.Interior.color = iColor Then theSum = theSum + 1
Next c
RangeInteriorColorCount = theSum
End Function

[ucijoinhitro]

Spendid..Works awesome. great post. infinite points.
Thanks ..Thanks...Thanks for ever.




You can do it like this or use the function as a UDF after NetToVBA.

When testing, clear the blank columns and rows that aren't really blank and then click save and then run DoTwoSubs.