SaeidN Posted September 4, 2022 Posted September 4, 2022 (edited) Hi, Let's say we have an excel file like below (each space means next cell): number county capital states color population no1 USA Washington 50 red 328 no2 Canada Ottawa 7 orange 38 no3 Germany Berlin 15 white 40 no4 Norway Oslo 5 black 10 I already have a 2D array which includes country and color in its 2 columns, like this: $dataArray = [["Germany","pink"],["USA", "gray"],["Norway","yellow"]] I want the code to update the colors in each cell for each country. I honestly don't know where to start. It's so confusing to match the countries and update the related cell. I appreciate your help. Edited September 4, 2022 by SaeidN
Subz Posted September 4, 2022 Posted September 4, 2022 Basic example: #include <Array.au3> #include <Excel.au3> Local $iBlack = 1 Local $iWhite = 2 Local $iRed = 3 Local $iOrange = 45 Local $aTeamColors = [["USA",$iRed],["Canada",$iOrange],["Germany",$iWhite],["Norway",$iBlack]] Local $oExcel = _Excel_Open() If @error Then Exit Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Teams.xlsx") If @error Then Exit For $i = 0 To UBound($aTeamColors) - 1 $aFindAll = _Excel_RangeFind($oWorkbook, $aTeamColors[$i][0]) If @error Then ContinueLoop For $j = 0 To UBound($aFindAll) - 1 $iRow = Number(StringReplace($aFindAll[$j][2], "$B$", "")) $oWorkbook.ActiveSheet.Range("E" & $iRow).Select With $oExcel.Selection.Interior .Pattern = 1 .PatternColorIndex = -4105 .ColorIndex = $aTeamColors[$i][1] .TintAndShade = 0 .PatternTintAndShade = 0 EndWith Next Next robertocm 1
SaeidN Posted September 6, 2022 Author Posted September 6, 2022 What does this do? "$B$" and also everything after "Selection.Interior" This code change the colors of each cell, how would I update the values?
Subz Posted September 6, 2022 Posted September 6, 2022 $B$ is the "Address of the cell" see _Excel_RangeFind for results. You can see the results by adding _ArrayDisplay($aFindAll) below _Excel_RangeFind(...) function. For "Selection.Interior" you can compose this within Excel by using the following: Open Excel » Developer tab Click: Record Macro Name: Macro1 Perform tasks you want to perform, change font color etc.. Click: Stop Recording Click: Macros Select: Macro1 Click: Edit You then need to copy the info into AutoIt and adjust accordingly. For example if you change font color to red, you would see a result like: With Selection.Font .Color = -16776961 .TintAndShade = 0 End With However I prefer using ColorIndex for simple colors (see What are the 56 ColorIndex colors in Excel - ExcelSuperSite) You then change the code in AutoIt to something like: With $oExcel.Selection.Font .ColorIndex = $aTeamColors[$i][1] .TintAndShade = 0 EndWith
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now