Jump to content

Recommended Posts

Posted (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 by SaeidN
Posted

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

 

Posted

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?

Posted

$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

 

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
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...