excel formatting (1 Viewer)

Jovial

Registered User.
Local time
Today, 10:57
Joined
Jan 21, 2015
Messages
29
Hello,
I have a excel file generated from access. After exporting i have to do some formatting which i tried to do though excel automation. I need look the each cell value of column E in the same column. If it exists highlight and make it bold.
Here is my code snippet.
Code:
'Find the last row
      lstRow2 = .Application.Cells(.Rows.Count, "E").End(xlUp).Row
      Debug.Print "lastRow:" & lstRow2
'Initialize the first row
      Set frstRow = 3
      
Do Until lstRow2 = frstRow
'check if last row is empty then go to lastrow -1
           If .Application.Rows("lstRow2:5") = " " Then
              lstRow2 = lstRow2 - 1
'search the value in same column and if it is present, make it bold and 'highlight it. I don't how to write the code for this part :banghead:
           Else if (search it in the same column (E)) = true then
               highlight and bold it
               lstRow2 = lstRow2 - 1
           End if
loop
Please help me!!!
 

sxschech

Registered User.
Local time
Today, 10:57
Joined
Mar 2, 2010
Messages
791
Not tested and need to change cell references and colors to your variables - something like this:

Code:
'Conditional Formatting
For Each cell In .Range(.cells(2, 5), .cells(lastrow, 5))
    if cell.value<> " " Then
        .Range(.cells(cell.row, 1), .cells(cell.row, lastcol)).Font.Bold = True
        .Range(.cells(cell.row, 1), .cells(cell.row, lastcol)).Interior.Color = RGB(221, 217, 196) 'Change to color you prefer
    endif
Next cell
 

Jovial

Registered User.
Local time
Today, 10:57
Joined
Jan 21, 2015
Messages
29
Thank you for your reply.
I want to do formatting only if there is a match between the cells in column.For this I have to check each and every cell in column E. For eg: I have data in Column E AS shown below
Column E
1234
678

678
890
1234

First find the match in Column E then only format so the final result will be
Column E
1234
678

678
890
1234
 

sxschech

Registered User.
Local time
Today, 10:57
Joined
Mar 2, 2010
Messages
791
I don't have the code with me, I'm sure there is a better way, but what I ended up doing in a case like that was adding a column to evaluate whether the number appeared more than once and assign it a 1 or 0, then in the code the looping would look at that col and if it was 1 do the bold, otherwise leave as is. Then after the formatting was done, deleted the col. I can try to find the code tomorrow.
 

Jovial

Registered User.
Local time
Today, 10:57
Joined
Jan 21, 2015
Messages
29
Thank you for replying. I spent a lot of time on this but still could not figure out.:banghead:
Waiting for your code :)
 

sxschech

Registered User.
Local time
Today, 10:57
Joined
Mar 2, 2010
Messages
791
Did a bit of searching and found a way to color the duplicate cells without having to create a helper col. It is a bit hybridized since I took code from various sites and fitted it into my existing code, hopefully you will be able to make adjustments to your code variable and object names. Obviously this is only the piece for that concept so will not work without being integrated into your existing code. When using the excel functions in Access, they need to be preceeded by the object name. My code is using
Code:
Set objapp = CreateObject("Excel.Application")
so that is why you will see
objapp.countif
in the code, adjust accordingly to what you set your object as.

Code:
'Code to highlight duplicate entry
'http://www.mrexcel.com/forum/excel-questions/606657-highlighting-duplicate-entries-using-visual-basic-applications.html
'Refer to Excel Function
'Use the item defined as create object
'https://msdn.microsoft.com/en-us/library/dd789430%28v=office.12%29.aspx
'Code to choose cell color
'http://dmcritchie.mvps.org/excel/colors.htm

'Conditional Formatting
    indexcolor = 6
    Set myrange = .Range(.Cells(2, 5), .Cells(lastrow, 5))
    For Each cell In .Range(.Cells(2, 5), .Cells(lastrow, 5))
        If objapp.countif(myrange, cell) > 1 Then
            If objapp.countif(.Range("E2:E" & cell.Row), cell) = 1 Then
                cell.Interior.ColorIndex = indexcolor
            Else
                cell.Interior.ColorIndex = myrange.Cells(objapp.Match(cell.Value, myrange, False), 1).Interior.ColorIndex
            End If
        End If
    Next cell
 

Users who are viewing this thread

Top Bottom