Jump to content
spoo

How to fetch the entire row in excel which is highlighted

Recommended Posts

image.png.9ac9376859d990aa545abbc29b816140.png

 

Hello guys,

How to get the row which is highlighted in yellow color.

that is row 44 and 49

 

Thanks in advance

 

Share this post


Link to post
Share on other sites

If you are looking for yellow cells then the answer is: No, the UDF does not support the needed FindFormat function.
I check if you can filter on color and then just read the filtered lines.

Another way is to use COM without the UDF as described here:
https://docs.microsoft.com/en-us/office/vba/api/excel.cellformat


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

This example works for me (for red cells):

#include <Excel.au3>

; Create workbook with 3 rows and 3 columns. Set cells A1 and A3 to red
Global $aUsedRow, $sMaxUsedColumn
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)
Global $aValues = [[11, 21, 31], [12, 22, 32], [13, 23, 33]]
_Excel_RangeWrite($oWorkbook, 1, $aValues)
Global $iRed = 234 ; Hex 0xEA
$oWorkbook.Activesheet.Range("A1").Interior.Color = $iRed
$oWorkbook.Activesheet.Range("A3").Interior.Color = $iRed

; Get the max used range of the active sheet
$sMaxUsedColumn = _Excel_ColumnToLetter($oWorkbook.ActiveSheet.Usedrange.Columns.Count)
; Loop through all rows and check column A for a red cell. If found, read all used cells of this row and display the array.
For $i = 1 To $oWorkbook.ActiveSheet.Usedrange.Rows.Count
    If $oWorkbook.Activesheet.Range("A" & $i).Interior.Color = $iRed Then
        $aUsedRow = _Excel_RangeRead($oWorkbook, $oWorkbook.Activesheet, $oWorkbook.Activesheet.Range("A" & $i & ":" & $sMaxUsedColumn & $i))
        _ArrayDisplay($ausedRow)
    EndIf
Next

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Posted (edited)

@water

Thanks for the reply,

I will try  and let you know if that worked

 

Edited by spoo

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...